SQL statement to find whole words

Question

I have a table with a varchar(max) field, and I need to perform text searches on it. Specifically, I need to find whole words. I have tried

Select * From MyTable 
Where Col = @SearchTxt
Or    Col Like @SearchTxt + ' %'
Or    Col Like '% ' + @SearchTxt + ' %'
Or    Col Like '% ' + @SearchTxt

This works to a certain extent, but it does not give me the instances where the SearchTxt is before or after punctuations (like SearchTxt! or SearchTxt,)

Is there a reasonably easy way to get what I'm looking for, without having a whole bunch of where clauses? Also, the column is not indexed, and although speed is not the primary concern, I want the query to run reasonably fast

Answer 1

You could do something like this:

SELECT <ColumnsList>
FROM MyTable
WHERE Col = @SearchText
OR Col LIKE @SearchTxt + '[^A-Za-z]%'
OR Col LIKE '%[^A-Za-z]' + @SearchTxt + '[^A-Za-z]%'
OR Col LIKE '%[^A-Za-z]' + @SearchTxt

For further reading, Here's Microsoft's documentation on Pattern Matching in Search Conditions