SQLite query, 'LIKE'

问题:

I am trying to retrieve information from my database.
I have words like Lim Won Mong and Limited.

If I do my query, SELECT name FROM memberdb WHERE name LIKE '%LIM%', it displays both Lim Won Mong and Limited and I only want data from Lim Won Mong.

How should I rewrite my query?

回答1:

Execute following Query:

select name from memberdb where name like '% LIM %' OR name like "LIM %" OR name like "% LIM" OR name like "LIM"

回答2:

If you want to match the word "lim" (so it matches "a lim", "lim a", "a lim a", and "lim", but not "limit" or "alim"), you can use the SQL REGEXP keyword, and use word boundaries ([[:<:]] and [[:>:]]). These match whitespace (spaces, tabs, newlines), string start/end, and some punctuation, as well.

Something like this:

SELECT name FROM memberdb WHERE name REGEXP '[[:<:]]LIM[[:>:]]'

Note that REGEXP is NOT case-sensitive (unless using binary strings).

If you want it to match ONLY spaces, you can still use REGEXP; this will match either the start of the string or a space, then "lim", then a space or the end of the string:

SELECT name FROM memberdb WHERE name REGEXP '(^| )LIM( |$)'

Both solutions have been tested on SQL versions which support REGEXP (SQLite 3+; all of Android uses 3.5.9 or higher).

回答3:

Try this one:

SELECT name 
FROM MEMBERDB 
WHERE name LIKE 'LIM %' OR name LIKE '% LIM'
OR name LIKE '% LIM %' OR name LIKE 'LIM';

Suppose you have data something like this:

'LIMITED'
'Lim Won Mong'
'Wang Tat Lim'
'ELIM TED'
'lim'
'Wang LIM tim'

This query will return you only following data:

'Lim Won Mong'
'Wang Tat Lim'
'lim'
'Wang LIM tim'

See this SQLFiddle