Word unscrambler in MySQL

Let’s say I have the input string “Hello” I want to be able to retrieve these output words: hole, lol, eh, hell All the output words letters should be contained in the input string.

How I should be able to achieve that? I’ve tried to use like:

SELECT * FROM words WHERE word LIKE (%h%) OR (%e%) OR (%l%) OR (%l%) OR (%o%)

But this returns words such as: hi، loop,etc.. but I need the all the letters to be contained in the input string

Answer

You can use REGEXP pattern matching for this task. This will match all words that contain characters from the required set.

SELECT * 
FROM words 
WHERE word REGEXP ('[helo]+')

Check the demo here.

Note: the REGEXP function is not case sensitive.

---

EDIT. Matching only the characters contained in the set of characters.

In this case you can look for words that do not match all the other characters:

SELECT * 
FROM words 
WHERE word NOT REGEXP ('[^helo]+')

Check the demo here.