Word Finder regular expression, PHP & mySQL

Question

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.

possible duplicate of [Negative backreferences in MySQL REGEXP](http://stackoverflow.com/questions/4122393/negative-backreferences-in-mysql-regexp) — Alix Axel, May 23 '11 at 00:19
The solution is extremely convoluted with pure MySQL alone, would it be an option to use MySQL to filter the easiest part and PHP for the rest? — Alix Axel, May 23 '11 at 01:02

BenMorel · Accepted Answer · 2011-05-24T15:13:25.073

1

Try this one:

SELECT word
FROM words
WHERE word REGEXP '^[ybilteh]*cruel[ybilteh]*$'
AND word != 'cruel';

UPDATE: let's go refining with PHP, what about this?

$word = 'cruel';
$letters = 'ybilteh';

$items = array("anticruelty", "crueler", "cruelest",
    "crueller", "cruellest", "cruelly", "cruelness",
    "cruelnesses", "cruelties", "cruelty");

$letters = str_split($letters);
foreach ($items as $item) {
    $list = $letters;
    // remove the original word (once)
    $thisItem = preg_replace("/$word/", '', $item, 1); 
    for ($i=0; $i<strlen($thisItem); $i++) {
        $index = array_search($thisItem[$i], $list);
        if ($index === false) {
            continue 2; // letter not available
        }
        unset($list[$index]); // remove the letter from the list
    }
    echo "$item\n"; // passed!
}

Returns: cruelly, cruelty

You might probably find a better/simpler approach, but that should do the trick!

edited May 24 '11 at 15:13

answered May 22 '11 at 23:26

BenMorel

34,448
50
182
322

Works great, but now I have this problem. For when a word is simply a letter. For example: Word: T Letters: ybilteh Returns the word BEET (Double e) – Robert Ross May 22 '11 at 23:34
You mean that each letter may appear only once? – BenMorel May 22 '11 at 23:49
No it just match the amount of the letter given. It returns BEET when only 1 "E" is present. I realize the regular expression can't do [ee]. Need to figure out a away around it – Robert Ross May 23 '11 at 00:12
I can't think of any way to do that with MySQL, as advised by Alix above, you might want to use a MySQL query (this one) to get a raw list, then filter it with PHP. – BenMorel May 24 '11 at 14:33
awesome. I have something similar but this looks a bit cleaner. Thanks! – Robert Ross May 24 '11 at 19:47

Word Finder regular expression, PHP & mySQL

1 Answers1