April 29, 2011

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:

Sql Server String Search

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


Javin Paul said...

How come I miss this interesting blog , I must say Dzone is doing very good job to bringing all good blogs together otherwise I never know about such a great resource about SQL, to be frank man I only knew about Like operator and RegEx way and the two other way you showed was something new to me, thanks a lot for such a nice site.

10 mysql commands to learn

Madhivanan said...

Thanks Javin for the feedback

Al said...

What's the more efficient one to use?

Madhivanan said...

Al, every method will do table scan.