Querying Where Condition to Character Length

querying WHERE condition to character length?

Sorry, I wasn't sure which SQL platform you're talking about:

In MySQL:

$query = ("SELECT * FROM $db WHERE conditions AND LENGTH(col_name) = 3");

in MSSQL

$query = ("SELECT * FROM $db WHERE conditions AND LEN(col_name) = 3");

The LENGTH() (MySQL) or LEN() (MSSQL) function will return the length of a string in a column that you can use as a condition in your WHERE clause.

Edit

I know this is really old but thought I'd expand my answer because, as Paulo Bueno rightly pointed out, you're most likely wanting the number of characters as opposed to the number of bytes. Thanks Paulo.

So, for MySQL there's the CHAR_LENGTH(). The following example highlights the difference between LENGTH() an CHAR_LENGTH():

CREATE TABLE words (
    word VARCHAR(100)
) ENGINE INNODB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO words(word) VALUES('快樂'), ('happy'), ('hayır');

SELECT word, LENGTH(word) as num_bytes, CHAR_LENGTH(word) AS num_characters FROM words;

+--------+-----------+----------------+
| word   | num_bytes | num_characters |
+--------+-----------+----------------+
| 快樂    |         6 |              2 |
| happy  |         5 |              5 |
| hayır  |         6 |              5 |
+--------+-----------+----------------+

Be careful if you're dealing with multi-byte characters.

Select something that has more/less than x character

If you are using SQL Server, Use the LEN (Length) function:

SELECT EmployeeName FROM EmployeeTable WHERE LEN(EmployeeName) > 4

MSDN for it states:

Returns the number of characters of the specified string expression,

excluding trailing blanks.

Here's the link to the MSDN

For oracle/plsql you can use Length(), mysql also uses Length.

Here is the Oracle documentation:

http://www.techonthenet.com/oracle/functions/length.php

And here is the mySQL Documentation of Length(string):

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length

For PostgreSQL, you can use length(string) or char_length(string). Here is the PostgreSQL documentation:

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-SQL

How to select data items of a certain length?

If you are bound to use a specific RDBMS then the solution is easy.

Use the LENGTH function.

Depending upon your database the length function can be LEN, Length, CarLength. Just search google for it.

According to your question

How do I select the row of a column such that the row size is <= 5 ?
Is there a query for this which will work on most/all databases ?

solution can be

SELECT * FROM TableName WHERE LENGTH(name) <= 5

If you want something that can work with almost all the database and I assume that the length of your string that you want to fetch is of a significant small length. Example 5 or 8 characters then you can use something like this

 SELECT * 
 FROM tab
 WHERE
    colName LIKE ''
 OR colName LIKE '_' 
 OR colName LIKE '__'
 OR colName LIKE '___'
 OR colName LIKE '____'
 OR colName LIKE '_____'

This works with almost all major DBMS.

see example:

SQL Server

MySQL

Oracle

Postgre SQL

SQLite

MySQL - How to select data by string length

You are looking for CHAR_LENGTH() to get the number of characters in a string.

For multi-byte charsets LENGTH() will give you the number of bytes the string occupies, while CHAR_LENGTH() will return the number of characters.

SQL SELECT where cell is a certain length and includes specific characters

You can use CHAR_LENGTH function along with LIKE:

SELECT * FROM Table WHERE name LIKE '%.%' AND CHAR_LENGTH(name) <= 5 LIMIT 1

SQL query to display the length and first 3 characters of ename column in emp table

Hi Shanu,
You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column.

SELECT LEN(column_name) FROM table_name;

And you can use SUBSTRING or SUBSTR() function go get first three characters of a column.

SUBSTRING( string, start_position, length );
SELECT SUBSTRING( column_name, 1, 3 ) FROM table_name;

To get both together use concatenation operator,

 SELECT LEN(column_name)||SUBSTRING( column_name, 1, 3 ) FROM table_name;

Hope you got what you need. Any issues, feel free to ask

oracle create index on column length with condition

Try

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

• The function invoked involve in the index expression must be deterministic. It means that for the same input, the function always
  returns the same result.

• The query optimizer can use a function-based index for cost-based optimization, not for rule-based optimization. Therefore, it does not use a function-based index until you analyze the index itself by invoking either DBMS_STATS.GATHER_TABLE_STATS or DBMS_STATS.GATHER_SCHEMA_STATS.

Or you can use Hint.

SELECT /*+ index(msisdn_data imsi_idx) */ * 
FROM msisdn_data WHERE LENGTH(imsi) <> 15;

For more information on why the function-based index is not used in your query, refer the link

http://www.dba-oracle.com/t_statistics_function_based_index.htm

---

Related Topics