Wednesday, February 1, 2012

LIKE operator "issue"

Hi
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, '[', '[[]')