By Patch ~ June 5th, 2013. Filed under: Oracle, SQL.
We sometimes use wildcard characters in the values of our columns. This makes it hard to find just these records. When looking for these records you want to search for the wildcard character, but this will return the other records as well.
I have created a simple table like this
create table hardtofind ( text varchar2(100) );
And I have inserted a couple of rows:
insert into hardtofind(text) values ('can easily be found'); insert into hardtofind(text) values ('hard_to_find'); insert into hardtofind(text) values ('also%hard%to%find');
If I would like to find the record containing the underscore character (’hard_to_find’) my first idea would be to just search for that character in the column. Something like:
select * from hardtofind where 1=1 and text like '%_%';
This will return all the rows in the table:
TEXT ---------------------- can easily be found hard_to_find also%hard%to%find
How can I search for wildcard characters without using them as a wildcard. One idea is to use the INSTR function to check for the existence of this character:
select * from hardtofind where 1=1 and instr(text, '_') > 0;
TEXT ---------------------- hard_to_find
But ofcourse the SQL language provides us with a way to escape the special characters. All you have to do is tell the engine when you want to escape the character:
select * from hardtofind where 1=1 and text like '%\_%' escape '\';
I used the version with the INSTR function but I figured SQL should provide us with means to escape special characters. Doing a little searching led me to this.
As always, there’s multiple ways to get to the same result.