SQL Query to include/exclude characters

Question

I have a dictionary table 'dictionary', where column 'word' contains the list of all English words.

I want to find all words that contain specific alphabet characters only and exclude the rest of the alphabet. I am able to do so (see the example below), but as you can see, it is downright ugly.

EXAMPLE

Currently to find all words that contain letters 'a', 'b', 'c', 'x', 'y', 'z', but exlude rest of the alphabet letters I do this:

SELECT word 
FROM   dictionary
WHERE  ( 
          word LIKE '%a%' 
          OR word LIKE '%b%' 
          OR word LIKE '%c%' 
          OR word LIKE '%x%' 
          OR word LIKE '%y%' 
          OR word LIKE '%z%' 
       ) AND ( 
             word NOT LIKE '%d%' 
             AND word NOT LIKE '%e%' 
             AND word NOT LIKE '%f%' 
             AND word NOT LIKE '%g%' 
             AND word NOT LIKE '%h%' 
             AND word NOT LIKE '%i%' 
             AND word NOT LIKE '%j%' 
             AND word NOT LIKE '%k%' 
             AND word NOT LIKE '%l%' 
             AND word NOT LIKE '%m%' 
             AND word NOT LIKE '%n%' 
             AND word NOT LIKE '%o%' 
             AND word NOT LIKE '%p%' 
             AND word NOT LIKE '%q%' 
             AND word NOT LIKE '%r%' 
             AND word NOT LIKE '%s%' 
             AND word NOT LIKE '%t%' 
             AND word NOT LIKE '%u%' 
             AND word NOT LIKE '%v%' 
             AND word NOT LIKE '%w%' ) 

Any way to accomplish this task using some form of regex or other optimization? Any tricks or hints would be much appreciated.

Answer 1

You can achieve it using REGEXP

SELECT `word `
FROM `dictionary`
WHERE `word` REGEXP '[abcxyzABCXYZ]'
    AND `word` NOT REGEXP '[defghijklmnopqrstuvwDEFGHIJKLMNOPQRSTUVW]'