18

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?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Lene
  • 519
  • 2
  • 4
  • 17

3 Answers3

41

Execute following Query:

select name from memberdb where name like '% LIM %' OR name like "LIM %" OR name like "% LIM" OR name like "LIM"
jeet
  • 29,001
  • 6
  • 52
  • 53
  • 1
    Note that this would not match if the string was simply "lim". (This would still technically be a whole word.) – Cat Sep 27 '12 at 04:18
4

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

Himanshu
  • 31,810
  • 31
  • 111
  • 133
4

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

Cat
  • 66,919
  • 24
  • 133
  • 141
  • Trying this on android and am getting an error when trying to run REGEXP keyword. It is throwing "SQLiteException: ICU error: uregex_open()" as an exception. Any ideas what could cause this? – ocross Dec 14 '15 at 22:54
  • @ocross Nope, doesn't ring a bell. You'll probably want to start a new question. – Cat Dec 14 '15 at 23:03
  • Okay thanks did you have to do anything specific to get REGEXP keyword to work with Android. Reading around it sounds like it is not supported by default. – ocross Dec 14 '15 at 23:07