MySQL，REGEXP - 查找仅包含以下确切字母的单词 [英] MySQL, REGEXP - Find Words Which Contain Only The Following Exact Letters

问题描述

我有一个特定长度的英语单词数据库.例如表 us_6 包含长度为 6 的英文单词.

I have a database of English words of a certain length. For example table us_6 contains English words with length of 6.

现在，我想在表格中搜索仅包含某些字母的单词.例如，我想搜索包含字母 vleoyl 的单词.我使用 REGEXP 进行了搜索.这是我的查询:

Now, I want to search the table for words that contains only certain letters. For example, I want to search for words that contains letters vleoyl. I did the search using REGEXP. Here's my query:

    SELECT word FROM us_6 WHERE
    word REGEXP 'v' AND
    word REGEXP 'l' AND
    word REGEXP 'e' AND
    word REGEXP 'o' AND
    word REGEXP 'y' AND
    word REGEXP 'l'

结果返回正确的单词，如 lovely 和 volley 但它也返回其他单词.查询结果如下:

The result returns correct words like lovely and volley but it also returns other words. Here's the result of the query:

    lovely
    loveys
    overly
    volley

你能帮我解决这个问题吗?我只想要包含完全提供的字母的单词.例如，vleoyl 应该只返回 lovely 和 volley.

Can you help me with this? I only want words that contains exactly the letters provide. For example, vleoyl should only return lovely and volley.

推荐答案

这里的问题是您要检查 l 是否存在两次.这与……它包含一个 l.是的，仍然包含一个 l."它没有检查其中的两个.这是一个替代方案...

The problem here is that you're checking for the presence of l twice. That's the same as... "It contains an l. Yep, still contains an l." It's not checking for two of them. Here's an alternative...

SELECT word FROM us_6 WHERE
word REGEXP 'v' AND
word REGEXP 'l.*l' AND
word REGEXP 'e' AND
word REGEXP 'o' AND
word REGEXP 'y'

这应该匹配包含一个v、两个l、一个e、一个o的单词, 和一个 y.

This should match all words containing a v, two l's, an e, an o, and a y.

因此，每隔一次出现相同的字母，只需将另一个 .*letter 附加到查询中.例如，lullaby 需要以下查询:

So, every other occurrence of the same letter, just append another .*letter to the query. For example, lullaby needs the following query:

    SELECT word FROM us_7 WHERE
    word REGEXP 'l.*l.*l' AND
    word REGEXP 'u' AND
    word REGEXP 'a' AND
    word REGEXP 'b' AND
    word REGEXP 'y'

看看我如何添加 3 个 *.l 因为在单词 lullaby 中出现了 3 次 l.

See how I add 3 *.l because there are 3 occurrences of l in the word lullaby.

同样的事情也可以用 LIKE 而不是 REGEXP 来完成.这是原始问题的等效查询...

The same thing can also be accomplished with LIKE instead of REGEXP. Here's an equivalent query for the original question...

SELECT word FROM us_6 WHERE
word LIKE '%v%' AND
word LIKE '%l%l%' AND
word LIKE '%e%' AND
word LIKE '%o%' AND
word LIKE '%y%'

这篇关于MySQL，REGEXP - 查找仅包含以下确切字母的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！