querying WHERE condition to character length?

Question

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?

93196.93 · Accepted Answer · 2015-12-17T13:59:52.940

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.

The MySQL syntax is the same as the SQLite syntax. Worked great for me. — Jon, Nov 12 '13 at 17:54
Beware! In multi-byte charsets it may be different. See [this](http://stackoverflow.com/questions/1870937/mysql-how-to-select-data-by-string-length) — Paulo Bueno, Jun 17 '15 at 10:56
In SQL Server (MS SQL), the LEN function will return number of characters but it will not count trailing spaces. So the solution might be to add non-space character at the end, and then subtract 1 from the result of LEN. Source (https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver15#remarks). — Lech Osiński, May 11 '22 at 10:55

score 9 · Answer 2 · edited Aug 22 '18 at 13:41

9

I think you want this:

select *
from dbo.table
where DATALENGTH(column_name) = 3

edited Aug 22 '18 at 13:41

I159

29,741
31
97
132

answered Jul 13 '16 at 14:58

Irish Lass

109
1
2

DATALENGHT takes into account the additionaly bytes used by unicode characters – Marcel Aug 24 '18 at 12:28

score -1 · Answer 3 · answered Jul 24 '11 at 14:04

-1

SELECT *
   FROM   my_table
   WHERE  substr(my_field,1,5) = "abcde";

answered Jul 24 '11 at 14:04

ennuikiller

46,381
14
112
137

querying WHERE condition to character length?

3 Answers3

Linked