Problem
I have a database with a lot of words, but I only want to show the records where the character length is equal to a certain amount (example 3):
$query = ("SELECT * FROM $db WHERE conditions AND length = 3");
However, this does not work… could someone please explain me how to use the correct query?
Asked by faq
Solution #1
I’m sorry, but I’m not sure which SQL platform you’re referring to:
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) functions return the length of a text in a column, which you can use as a WHERE clause condition.
Edit
I know this is an old question, but I wanted to expand on my response because, as Paulo Bueno correctly pointed out, you’re probably looking for the number of characters rather than the number of bytes. Paulo, thank you so much.
So there’s the CHAR LENGTH variable in MySQL (). The difference between LENGTH() and CHAR LENGTH() is seen in the following example:
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 |
+--------+-----------+----------------+
If you’re dealing with multi-byte characters, be cautious.
Answered by 93196.93
Solution #2
This is what I believe you want:
select *
from dbo.table
where DATALENGTH(column_name) = 3
Answered by Irish Lass
Solution #3
SELECT *
FROM my_table
WHERE substr(my_field,1,5) = "abcde";
Answered by ennuikiller
Post is based on https://stackoverflow.com/questions/6807231/querying-where-condition-to-character-length