Match only entire words with LIKE?

问题:

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.

回答1:

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)

回答2:

WHERE (@string LIKE 'doc %' OR @string LIKE '% doc' OR @string LIKE '% doc %' OR @string = 'doc')

回答3:

You could use some ORs, LIKE '% doc %' OR LIKE 'doc %' OR LIKE '% doc' OR LIKE 'doc'

回答4:

you can just use below condition:

(' '+YOUR_FIELD_NAME+' ') like '% doc %'

it works faster than other solutions.

回答5:

What about NOT LIKE '%doc%'?