SQL Server: Search Similar String in a Table

There are may ways to look for similar strings in a SQL Server column. The most common  method is to make use of LIKE operator. Let us see the different ways to look for 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 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 charindex and patindex look for the match and return its position in the string. All the above four methods would return the same result as shown below:

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan