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, '[', '[[]')
Subscribe to:
Post Comments (Atom)
4 comments:
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
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
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.
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.
Post a Comment