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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "SQL Server: Search Similar String in a Table"
  1. Javin Paul said...
    June 1, 2011 at 6:29 AM

    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.

    Javin
    10 mysql commands to learn

  2. Madhivanan said...
    June 1, 2011 at 6:31 AM

    Thanks Javin for the feedback

  3. Al said...
    August 20, 2011 at 7:27 PM

    What's the more efficient one to use?

  4. Madhivanan said...
    August 21, 2011 at 10:41 PM

    Al, every method will do table scan.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions