Wednesday, February 1, 2012

LIKE operator "issue"

Please consider the below script

create table #t ( col varchar(40))

insert into #t values ('[Untitled].pdf')
insert into #t values ('Untitled].pdf')
insert into #t values ('^Untitled].pdf')
insert into #t values ('|Untitled].pdf')

Now the client runs the below SELECT statement

select * from #t where col like col

Ok, you ask why col LIKE col and not col=col. Well I really simplified the the query because it takes a parameter and needs to search sub string within a col base on CASE Expression.So as you can see the above script does not return the first row [Untitled].pdf. After some investigation it turns out than we cannot get rid of left bracket. The only real solution is the below script (thanks to Peter Larsson)

select * from #t where col like replace(col, '[', '[[]')


MichaƂ said...

It is not only the opening square bracket but also underscore which you have to be worried about as those are special characters.

If you try select * from #t where col like '_Untitled].pdf' you will get all four rows.

Something like
select * from #t where col like replace(replace(replace(col, '\', '\\'),'[', '\['), '_', '\_') escape '\'

although more complex will take care of all special characters

Doron said...

Hi Uri,

Interesting article about:

String Manipulation on large data using LIKE operator or PATINDEX


The Farber Consulting Group, Inc.

SQL Consulting Services