查询字符条件在哪里条件？ [英] 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）函数将返回a的长度可以在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正确指出的那样，您很可能想要字符数而不是字节数。谢谢Paulo。

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.

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