Tuesday, June 10, 2008

SQL and Underscore

Recently one of our QA told me that searching for a record with _ in it, won't work.
Actually it works, but the thing is that the '_' ( underscore ) is a reserved SQL character, and you have to know how to use it.

Originally the '_' ( underscore ) was designed to:
Let's say you want to find the word 'synopsys' in your table. But you don't if it was 'synopsys' or 'sinopsys' :)
In this case you should do this:




select Title from YourTable where title like 's_nopsys'




And this will find them both. (if any)

Back to Our case.
What our code looked like was something like this:




select Title from YourTable where title like '%_%'




Of course it returned everything from the table ... so QA was kinda right ... filter was not working.
But if QA would've known to search for '[_]' ... then it would've worked ...


It is doable, of course, not to have to add the '[' and the ']' in the search box ... but ... does it worth it?
If you can explain your 'client', how to use the '_' ( underscore ), and how to use the % sign ... in the search query ...
well maybe you don't have to change any of your code, because your 'client' might be happy to use these extra features.

If you can't talk your 'client' into this, you can try something like SQL's escape:




select Title from YourTable where title like '%\_%' escape '\'




or, add some extra character, because next time they'll search for '\' :)
so maybe do something like this:




select Title from YourTable where title like '%' + char(13) + '_%' escape char(13)




If you have some thoughts on this ... tell me about it ...

1 comment:

Radulii said...

What worked for me was a replace of the [ with [[] then replace _ with [_] and then % with [%] . no other escape chars needed and the user may use any characters they like including [].