查询 WHERE 条件到字符长度? [英] querying WHERE condition to character length?

问题描述

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

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?

推荐答案

抱歉，我不确定你说的是哪个 SQL 平台:

Sorry, I wasn't sure which SQL platform you're talking about:

在 MySQL 中:

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

在 MSSQL 中

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

LENGTH() (MySQL) 或 LEN() (MSSQL) 函数将返回列中字符串的长度，您可以将其用作 WHERE 子句中的条件.

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.

编辑

我知道这真的很旧，但我想我会扩展我的答案，因为正如 Paulo Bueno 正确指出的那样，您很可能需要字符数而不是字节数.谢谢保罗.

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.

所以，对于 MySQL，有 CHAR_LENGTH().以下示例突出显示 LENGTH() 和 CHAR_LENGTH() 之间的区别:

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.

这篇关于查询 WHERE 条件到字符长度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！