Various ways to search a string in SQL server

There are may ways to look for the similar strings in an SQL Server column.
The most common method is to make use of the LIKE operator. Let us have a look at the different ways to look for the similar string in a table.

Consider the following data:

declare @test table(data varchar(100))
insert into @test
select 'this is for testing' union all
select 'test entry' union all
select 'no way for this' union all
select 'nothing to be tested' union all
select 'welcome'

Suppose, you want to find out the data with the word ‘test’

Method 1 : Use LIKE operator

select data from @test
where data like '%test%'

Method 2 : Use CHARINDEX function

select data from @test
where charindex('test',data)>0

Method 3 : Use PATINDEX function

select data from @test
where patindex('%test%',data)>0

Method 4 : Use Regular expression

select data from @test
where data like '%[t][e][s][t]%'

Both, the charindex and the patindex looks for the match and returns its position in the string. All the above four methods would return the same result.
Ebook Download
View all
Learn
View all