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.