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

4 comments:

Michał Poręba 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

Regards,

Doron
The Farber Consulting Group, Inc.

SQL Consulting Services

Susan said...

You can definitely see your expertise within the paintings you write. The world hopes for more passionate writers like you who are not afraid to mention how they believe. All the time go after your heart. 192.168.l.254 is a private IP address that comes in a range booked from IANA (Internet Assigned Number Authority) for specific purposes, though in many cases users mistype the IP address as 192.168.1.254 where the character one is assumed as letter L.

Susan said...

You can definitely see your expertise within the paintings you write. The world hopes for more passionate writers like you who are not afraid to mention how they believe. All the time go after your heart. 192.168.l.254 is a private IP address that comes in a range booked from IANA (Internet Assigned Number Authority) for specific purposes, though in many cases users mistype the IP address as 192.168.1.254 where the character one is assumed as letter L.