Match only entire words with LIKE?

So 'awesome doc开发者_运维知识库ument' 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.

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)

you can just use below condition:

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

it works faster than other solutions.

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

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

LIKE is quite limited. I would suggest REGEXP, for example:

 columnName REGEXP 'doc[;.?!,") ]'

for example: awesome document wouldn't match, but awesome doc, doc awesome, awesome doc awesome will match.

It will also match an existing word in text followed by punctuation characters. You can try any example here: https://regexr.com/

None of the answers worked for me (SQL Server 2008 R2).

But I managed to get it to work using somewhat of the answers shown below..

SELECT * FROM myTable AS v
WHERE v.Note LIKE '%[^a-zA-Z0-9]CONTainers[^a-zA-Z0-9]%'

Note: v.Note is a TEXT data type

This worked for me with the following samples..

• containers
• CONTAINERS
• conTaInERS

But not when the word 'containers' was surrounded by other letters or numerics..

• 001containers001
• AAcontainersAA

Which is what the OP asked for in the first place.

You can use REGEX with "word boundary" MySql documentation

So, in your example : 'awesome document' REGEXP '\bdoc\b'

What about NOT LIKE '%doc%'?