将 WHERE 条件查询到字符长度？

我有一个包含大量单词的数据库，但是我只想选择那些字符长度等于给定数字的记录(例如案例3) :

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

但是这个不管用... 有人能告诉我正确的问题吗？

366081 次浏览
小开
最佳答案

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 *
FROM   my_table
WHERE  substr(my_field,1,5) = "abcde";
小开

I think you want this:

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

提交答案