SQL 查询 WHERE 条件到字符长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6807231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
querying WHERE condition to character length?
提问by faq
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):
我有一个包含大量单词的数据库，但我只想选择字符长度等于给定数字的那些记录（在示例案例 3 中）：
$query = ("SELECT * FROM $db WHERE conditions AND length = 3");
But this does not work... can someone show me the correct query?
但这不起作用......有人可以告诉我正确的查询吗？
回答by 93196.93
Sorry, I wasn't sure which SQL platform you're talking about:
抱歉，我不确定您说的是哪个 SQL 平台：
In MySQL:
在 MySQL 中：
$query = ("SELECT * FROM $db WHERE conditions AND LENGTH(col_name) = 3");
in MSSQL
在 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.
LENGTH() (MySQL) 或 LEN() (MSSQL) 函数将返回列中字符串的长度，您可以将其用作 WHERE 子句中的条件。
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.
我知道这真的很旧，但我想我会扩大我的答案，因为正如 Paulo Bueno 正确指出的那样，您很可能想要字符数而不是字节数。谢谢保罗。
So, for MySQL there's the CHAR_LENGTH(). The following example highlights the difference between LENGTH()an CHAR_LENGTH():
所以，对于 MySQL，有CHAR_LENGTH(). 下面的示例着重之间的差LENGTH()的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.
如果您正在处理多字节字符，请小心。
回答by Irish Lass
I think you want this:
我想你想要这个：
select *
from dbo.table
where DATALENGTH(column_name) = 3
回答by ennuikiller
SELECT *
FROM my_table
WHERE substr(my_field,1,5) = "abcde";
