Match only entire words with LIKE?

sql sqlite sql-like
Abhinav Sharma picture Abhinav Sharma · Jun 8, 2011 · Viewed 25k times · Source

So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with a like?

I'm using sqlite, so I hope I don't have to use something that isn't available.

Answer

YetAnotherUser picture YetAnotherUser · Jun 8, 2011

How about split it into four parts - 

[MyColumn] Like '% doc %' 
OR [MyColumn] Like '% doc' 
OR [MyColumn] Like 'doc %' 
OR [MyColumn] = 'doc'

Edit: An alternate approach (only for ascii chars) could be: 

'#'+[MyColumn]+'#' like '%[^a-z0-9]doc[^a-z0-9]%'

(You may want to take care of any special char as well)

It doesn't look like, but you may want to explore Full Text Search and Contains, in case that's more suitable for your situation.

See: - MSDN: [ ] (Wildcard - Character(s) to Match) (Transact-SQL)

Related questions

SQLite Like % and _

I can't figure out what the underscore character does in an SQLite like statement. The wildcard character, %, is probably the same as in most other SQL databases. So, what does the _ character do?

Read More
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 …

Read More
Return rows where first character is non-alpha

I'm trying to retrieve all columns that start with any non alpha characters in SQlite but can't seem to get it working. I've currently got this code, but it returns every row: SELECT * FROM TestTable WHERE TestNames NOT LIKE '[…

Read More