6

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

  • 2
    LIKE is not strong enough for this, but you could use REGEXP instead. It seems from the documentation that you have to supply the function yourself, but you will probably be able to find one - some details are in this answer – jkavalik Dec 13 '15 at 20:45
  • Thanks for your comment. REGEXP is really powerful and I'm sure it can fulfil my needs but REGEXP can be very expensive because SQL can't use index and queries may need much time to be executed and Our software runs on multi platform (Android & Windows) I hope implementing REGEXP function on them is an easy task. – Farhad Dec 14 '15 at 16:38
  • 2
    Well, the LIKE you showed cannot use index too (only prefix matches can). – jkavalik Dec 14 '15 at 19:27
  • 1
    The example is not very good by the way. Both 'Open' and 'Computers' have o and e. Unless you only want lower o (and exclude O) and you want the order to be "first-e-then-o" in the same word (and exclude "first-o-then-e") – ypercubeᵀᴹ May 6 '17 at 6:11
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:


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.

| improve this answer | |
0

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

| improve this answer | |
-1

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.

| improve this answer | |
  • Why the down vote? did you test the query? – indago Jun 29 '17 at 14:39
  • That would just select where the name column is a single word. The original question would allow for more than one word, as far as 'o' and 'e' appear on a single one. (BTW: the downvote isn't mine ;-) – joanolo Jul 9 '17 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.