带有sql的％的单词anagrammer [英] Word anagrammer with sql's %

问题描述

我需要创建一个脚本来搜索带有'空格'的单词，这些单词基本上在sql中是

$numberofblanks = 1; //max 13
$searchedword = "WORD";
$searchedwordsorted = "DORW";

给出的结果应该是:
WORD 世界 人群 剑 字 嫁妆 行 淹 嫁妆 罗迪

％word，w％ord，wo％rd，wor％d，word％都可以，但是对于包含2个或更多空格的更复杂的查询呢?
还想知道$ searchedwordsorted是否有帮助还是没有关系，这只是浪费我表中的空间.

非常感谢您的帮助.
.mike

解决方案

首先，我想更正您的问题中的错误.在查询中，您指的是_而不是%. %表示任意数量的字符(零个或多个).使用_表示一个字符.

现在开始解决方案...实际上，您不需要存储在数据库中的已排序单词.您可以这样做:

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 6
AND word LIKE '%W%'
AND word LIKE '%O%'
AND word LIKE '%R%'
AND word LIKE '%D%'

如果输入中包含重复的字母，则需要正确处理，以确保所有结果都包含所有重复的字母.例如，如果输入为FOO__，则需要检查每个单词是否同时匹配%F%和%O%O%.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND word LIKE '%F%'
AND word LIKE '%O%O%'

请注意，此方法将需要对表进行全面扫描，因此效率不是特别高.通过将每个单词的长度存储在单独的列中并为该列建立索引，可以稍微改善一下情况.

---

如果有sortedword，则可以通过在重复的字母之间省略%来提高性能，因为您知道它们会连续出现在sortedword中.由于可以减少失败比赛所需的回溯，因此可以提高性能.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%F%'
AND sortedword LIKE '%OO%'

---

另一种要求显示sortedword的方法如下:

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%D%O%R%W%'

同样，这需要对表进行全面扫描.同样，如果您重复输入字母，则不需要在它们之间使用%.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%F%OO%'

I need to create a script searching for words with 'blanks', which basically are % in sql.

$numberofblanks = 1; //max 13
$searchedword = "WORD";
$searchedwordsorted = "DORW";

Results given should be:
WORDY WORLD CROWD SWORD WORDS DOWRY ROWED DROWN DOWER ROWDY

%word, w%ord, wo%rd, wor%d, word% would do, but what about more complicated queries, with 2 or more blanks?
Also was wondering if $searchedwordsorted is any helpful or it doesn't really matter and it's just waste of space in my table.

Thank you kindly for your help guys.
.mike

解决方案

First I want to correct an error in your question. In your queries you mean _ not %. The % means any number of characters (zero or more). Use _ to mean exactly one character.

Now on to the solution... you don't actually need the sorted word stored in the database. You could just do this:

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 6
AND word LIKE '%W%'
AND word LIKE '%O%'
AND word LIKE '%R%'
AND word LIKE '%D%'

If you have duplicate letters in your input, need to handle this correctly to ensure that all results contain all the duplicated letters. For example if the input is FOO__ you need to check that each word matches both %F% and %O%O%.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND word LIKE '%F%'
AND word LIKE '%O%O%'

Note that this approach will require a full scan of the table so it will not be particularly efficient. You could improve things slightly by storing the length of each word in a separate column and indexing that column.

---

If you have sortedword then you can improve performance by omitting the % between duplicated letters since you know that they will appear consecutively in sortedword. This could improve performance bceause it reduces the amount of backtracking required for failed matches.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%F%'
AND sortedword LIKE '%OO%'

---

Another approach that requires sortedword to be present is as follows:

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%D%O%R%W%'

Again this requires a full scan of the table. Again, if you have repeated letters you don't need the % between them.

SELECT word
FROM dictionary
WHERE CHAR_LENGTH(word) = 5
AND sortedword LIKE '%F%OO%'

这篇关于带有sql的％的单词anagrammer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！