T-SQL : Search for string or phrase in SQL Server database

In this post, let us see how to search for a string / phrase in SQL Server database using hybrid solution of T-SQL LIKE operator & R grep function. Currently the options that exists in SQL Server to perform a search operation are 

1. LIKE operator 
2. Using string functions CHARINDEX, PATINDEX 
3. Full text search 

Consider below example: To search and return only records with string "VAT" . Expected result is to return record 1,5 & 6.

DECLARE @Tmp TABLE (Id INT, Descrip VARCHAR(500))

INSERT @Tmp SELECT 1,'my VAT calculation is incorrect'

INSERT @Tmp SELECT 2,'Private number'

INSERT @Tmp SELECT 3,'Innnovation model'

INSERT @Tmp SELECT 4,'ELEVATE'

INSERT @Tmp SELECT 5,'total VAT'

INSERT @Tmp SELECT 6,'VAT'

SELECT * FROM @Tmp WHERE Descrip LIKE 'VAT'

SELECT * FROM @Tmp WHERE Descrip LIKE '%VAT'

SELECT * FROM @Tmp WHERE Descrip LIKE '%VAT%'

SELECT * FROM @Tmp WHERE Descrip LIKE '% VAT %'

SELECT * FROM @Tmp WHERE Descrip LIKE '% VAT'

As shown in above example, to do an exact search on string, there is no straight forward option using first two options mentioned above. However though it is possible with third option using  Full text CONTAINS predicate. Full text catalog, unique index & full text index has to be created on the table on which search operation needs to be performed.

If the exact search of string needs to be performed on the entire database then creating full text catalog, unique index & full text index on each and every table won’t be a viable option.

With the hybrid approach [T-SQL LIKE operator & R grep function], let us see various search types that can be performed [Pattern Search, Exact Search, Multi pattern search and other search scenario's  – based on collation, case sensitive/insensitive search and complex wildcard search].