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

Question

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

Answer 1

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