MySQL, REGEXP - Find Words Which Contain Only The Following Exact Letters

Question

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

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'

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

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.

Answer 1

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'

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

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'

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

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%'

Answer 2

Think outside the box!

Store lovely and volley as ellovy. That is, sort the letters, then do an exact match.

Build a table that maps

ellovy -> lovely
ellovy -> volley
ellowy -> yellow
elorvy -> overly
elovsy -> loveys 

And have a non-UNIQUE (not PRIMARY) key on the first column. (Probably have the second column be the PRIMARY KEY.

No need for REGEXP, OR, etc. And muuuuuch faster. Furthermore, it could be a single table for all word lengths.

Answer 3

I'm not seriously advocating this as a solution - instead I'd use application level code for this task (e.g. a bit of PHP), for which there must be myriad tutorials - but just for fun...

SELECT * FROM strings;
+-----------+--------+
| string_id | string |
+-----------+--------+
|         2 | lovely |
|         3 | volley |
|         1 | yellow |
+-----------+--------+

3 rows in set (0.00 sec)SELECT DISTINCT CONCAT(a.x,b.x,c.x,d.x,e.x,f.x) needle
  FROM 
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') a
  JOIN
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') b
  JOIN
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') c
  JOIN
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') d
  JOIN
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') e
  JOIN
     ( SELECT 'v' x UNION ALL SELECT 'o' UNION ALL SELECT 'l' UNION ALL SELECT 'l' UNION ALL SELECT 'e' UNION ALL SELECT 'y') f
  JOIN strings s
    ON s.string = CONVERT(CONCAT(a.x,b.x,c.x,d.x,e.x,f.x) USING utf8) ;

+--------+
| needle |
+--------+
| lovely |
| volley |
+--------+