Like predicate to match a whole word only

Question

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

Answer 1

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.

Answer 2

As Jkavalik commented, the LIKE query with a starting % could not be indexed.

LIKE filters can only use the characters before the first wild card during tree traversal. The remaining characters are just filter predicates that do not narrow the scanned index range.

and

A LIKE expression that starts with a wild card [...] cannot serve as an access predicate. The database has to scan the entire table if there are no other conditions that provide access predicates.

as if it were no index.

If your database is not "big", just use regular expressions. It will make your development easier, even if it may take some time to select all the rows.

If you still want to avoid them, consider using the GLOB operator. It's not as powerful as a regex, but better than LIKE. My suggestion.

SELECT id, name
    FROM minecraft
    WHERE name GLOB '*e*o*'
        AND name NOT GLOB '*e* *o*';

It's not as powerful because it wont match rows with a word like 'hello' not on the first position, like 'This hello wont match'.

Answer 3

The solution to your query will be just one simple query,

select id, name from minecraft
where name like '%e%o%' and instr(trim(name),' ')=0

Thats the shortest answer that works in sqlite.