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)
1 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
Post a Comment