Clever Geek Handbook

SQL expression for searching whole words

I have a table with a varchar (max) field and I need to search for text on it. Specifically, I need to find whole words. I 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 doesn't give me instances where SearchTxt is before or after punctuation (like SearchTxt! Or SearchTxt)

Is there a simple enough way to get what I'm looking for without having a whole bunch of articles? Also, the column is not indexed, and although speed is not the main issue, I want the query to be fast enough

+3


source to share


2 answers


You can 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, Microsoft's documentation on Pattern Matching in Search Terms

+2


source 


    --Try this code it may meet your requirement
DECLARE @SearchTxt Varchar(20)='SearchWord'    
SELECT TOP 1 * from #TempTable
WHERE CHARINDEX(@SearchTxt,CONCAT(Column1,Column2,Column3,Column4))>0


0


source






More articles:


All Articles