Search for “whole word match” with SQL Server LIKE pattern

Does anyone have a LIKE pattern that matches whole words only?

It needs to account for spaces, punctuation, and start/end of string as word boundaries.

I am not using SQL Full Text Search as that is not available. I don't think it would be necessary for a simple keyword search when LIKE should be able to do the trick. However if anyone has tested performance of Full Text Search against LIKE patterns, I would be interested to hear.

Edit:

I got it to this stage, but it does not match start/end of string as a word boundary.

where DealTitle like '%[^a-zA-Z]pit[^a-zA-Z]%' 

I want this to match "pit" but not "spit" in a sentence or as a single word.

E.g. DealTitle might contain "a pit of despair" or "pit your wits" or "a pit" or "a pit." or "pit!" or just "pit".

Full text indexes is the answer.

The poor cousin alternative is

'.' + column + '.' LIKE '%[^a-z]pit[^a-z]%'

FYI unless you are using _CS collation, there is no need for a-zA-Z

you can just use below condition for whitespace delimiters:

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

it works faster and better than other solutions. so in your case it works fine with "a pit of despair" or "pit your wits" or "a pit" or "a pit." or just "pit", but not works for "pit!".

I think the recommended patterns exclude words with do not have any character at the beginning or at the end. I would use the following additional criteria.

where DealTitle like '%[^a-z]pit[^a-z]%' OR 
  DealTitle like 'pit[^a-z]%' OR 
  DealTitle like '%[^a-z]pit'

I hope it helps you guys!

Another simple alternative:

WHERE DealTitle like '%[^a-z]pit[^a-z]%' OR 
      DealTitle like '[^a-z]pit[^a-z]%' OR 
      DealTitle like '%[^a-z]pit[^a-z]'