Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

querying WHERE condition to character length?

I have a database with a large number of words but i want to select only those records where the character length is equal to a given number (in example case 3):

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

But this does not work... can someone show me the correct query?

like image 226
faq Avatar asked Jul 24 '11 14:07

faq


People also ask

How do I find the length of a character in SQL?

SQL Server LEN() Function The LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.

How do I query the length of a string in SQL?

Well, you can use the LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name.

How do you change character length in SQL?

Here is how to increase field length in MySQL. Let us say you have a VARCHAR column with length 20, and want to increase its length to 255. In this case, you need to use ALTER TABLE statement to increase column size. ALTER TABLE table_name MODIFY column_name varchar(new_length);


2 Answers

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.

like image 97
93196.93 Avatar answered Sep 21 '22 08:09

93196.93


I think you want this:

select * from dbo.table where DATALENGTH(column_name) = 3 
like image 21
Irish Lass Avatar answered Sep 22 '22 08:09

Irish Lass