Like predicate to match a whole word only I have a SQLite database with a table named minecraft.

+----+----------------------+
| id |         name         |
+----+----------------------+
|  1 | Pocket Mine MP       |
|  2 | Open Computers       |
|  3 | hubot minecraft skin |
|  4 | Terasology           |
|  5 | msm                  |
+----+----------------------+


I need to find all the records which have 'e' and 'o' in their 'name' field. Here is my Select query:

select * from minecraft where name like '%e%o%'


Here is the result of the above query:

+----+----------------+
| id |      name      |
+----+----------------+
|  2 | Open Computers |
|  4 | Terasology     |
+----+----------------+


The problem is that the Like predicate matches the entire value, not words. The row with id = 2 should not be matched, because all the criteria didn't happen in a single word ('e' is found in the first word and 'o' in the other word): Open Computers.



How should I change my Select query so it matches the only row with 'e' and 'o' in a single word?

+----+------------+
| id |    name    |
+----+------------+
|  4 | Terasology |
+----+------------+


Thanks
Your best bet is probably to use REGEXP, which needs some special installation prior to being usable. 

In that case, you'd use:

WHERE         
    name REGEXP 'e\w*o' 


This regular expression means: (a literal 'e') followed by (zero or more word characters) followed by (a literal 'o').

This approach won't shy away from cases like 'This hello will work'

References:


How do I use regex in a SQLite query?
Online Regular Expression tester - Regex101.com




Alternative:

If you can't install the required packages to have REGEXP available, you can probably use a user-defined function to perform the "word-by-word" LIKE check. Let's imagine it's called check_words_like(sentence, like_predicate), I'd use in the following fashion:

SELECT
    whatever
FROM
    (SELECT
        name, whatever
    FROM
        tbl
    WHERE
        name like '%e%o%'
    ) AS q1
WHERE
    check_words_like(q1.name, '%e%o%') ;


This still requires some C programming and interfacing, and might be as complicated as getting the right REGEXP package.