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

4 comments:

  1. 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

    ReplyDelete
  2. What's the more efficient one to use?

    ReplyDelete
  3. Al, every method will do table scan.

    ReplyDelete