MySQL 在任何地方查找包含所有字母的单词 [英] MySQL find words containing all letters at any place

问题描述

使用以下查询

SELECT * FROM words WHERE word REGEXP '(ord)'

• 这将返回像ford、lord、word
这样的词

如何包含出现这些字母但顺序不同的单词?

How can I include words where these letters appear but not in the same order?

• 例如adore、door、random

编辑:

成功了.

SELECT word, (
    IF(LOCATE('o', word) > 0, 1, 0) + 
    IF(LOCATE('r', word) > 0, 1, 0) +
    IF(LOCATE('z', word) > 0, 1, 0) +
    IF(LOCATE('a', word) > 0, 1, 0) +
    IF(LOCATE('d', word) > 0, 1, 0)) AS chars_present
from words
HAVING chars_present = 5

现在如何查询包含字母 r 两次的单词?

Now how would I query for words containing the letter r twice?

推荐答案

这可能是最快的:

SELECT *
    FROM words
    WHERE LOCATE('o', word)
      AND LOCATE('r', word)
      AND LOCATE('d', word);

mysql> SELECT city, state FROM us
      WHERE locate('o', city) AND locate('r', city) AND locate('d', city)
      LIMIT 11;
+---------------+-------+
| city          | state |
+---------------+-------+
| Irondale      | AL    |
| Oxford        | AL    |
| El Dorado     | AR    |
| Paragould     | AR    |
| Sherwood      | AR    |
| Goodyear      | AZ    |
| Safford       | AZ    |
| Alondra Park  | CA    |
| Anderson      | CA    |
| Arroyo Grande | CA    |
| Atascadero    | CA    |
+---------------+-------+
11 rows in set (0.00 sec)

如果你需要两个 r，对它们的测试将是 word REGEXP 'r.*r':

If you need two r, the test for them would be word REGEXP 'r.*r':

mysql> SELECT city, state FROM us
    ->           WHERE locate('o', city) AND city REGEXP 'r.*r' AND locate('d', city)
    ->           LIMIT 5;
+--------------------+-------+
| city               | state |
+--------------------+-------+
| Alondra Park       | CA    |
| Arroyo Grande      | CA    |
| Corte Madera       | CA    |
| Desert Hot Springs | CA    |
| Garden Grove       | CA    |
+--------------------+-------+

这篇关于MySQL 在任何地方查找包含所有字母的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！