I have a query that I'm trying to run against a database of words. The query must return a word that contains another word, and letters given. It is in PHP and mySQL.
For example:
Word Given: Cruel
Letters Given: abcdty
In the database, I need to find the word "Cruelty" based on the letters given, and the word given. It needs to works both ways. So if I had "atni" for letters, "Anticruel" would appear if it existed in the database.
I have it half working but the result given is not correct:
SELECT word
FROM words
WHERE LOCATE( "cruel", word ) >0
AND word != "cruel"
AND word
REGEXP '[ybilteh]'
The result set from this query:
"anticruelty"
"crueler"
"cruelest"
"crueller"
"cruellest"
"cruelly"
"cruelness"
"cruelnesses"
"cruelties"
"cruelty"
Update!!!
Thanks to Benjamin Morel, this is getting much closer.
This query:
SELECT word
FROM words
WHERE LOCATE( "t", word ) >0
AND word != "t"
AND word
REGEXP '^[ybilteh]*t[ybilteh]*$'
LIMIT 0 , 30
Finds words correctly. But also includes words with double letters. Such as "Beet". When only 1 "e" is available.