How to handle/use special characters like percent (%) and ampersand (&) in Oracle SQL queries

Question

I need to include the special character "%" in my LIKE clause in a SQL query, e.g.:

Select * From Some_Table Where Field_Name Like 'bla%bla&2';

How do I write that?

Solution 1

If you want to match Field_Name values that contain 'bla%bla&2', then you need to write this:

set define off
Select * From Some_Table Where Field_Name Like '%bla\%bla&2%' escape '\';

You get to specify which character you want to use to escape a following character (thanks should go to mathguy, not me). You also have to set define off to prevent sqlplus from trying to substitute values in a string.

If, however, you want to match Field_Name values that exactly equal the given string, then you do this instead:

set define off
Select * From Some_Table Where Field_Name = 'bla%bla&2';

Solution 2

If I am not mistakend you escape them with a backslash (\)

Select * From Some_Table Where Field_Name Like 'bla\%bla&2' ESCAPE '\';

Solution 3

Use escape \ to treat is a literal

SELECT *
  FROM Some_Table
 WHERE Field_Name LIKE 'blah\%'|| 'blah' ||'&'|| '2';

Comments

[mathguy]

You need to add ESCAPE '\' after the LIKE condition to let Oracle know what you are doing. It's not automatic.

[mathguy]

You need to add ESCAPE '\' after the LIKE condition to let Oracle know what you are doing. It's not automatic.

[mathguy]

Sorry, but no, that is not how you escape % in the LIKE condition. Best to delete this incorrect answer; someone already upvoted it, which means they think it is correct.

[mathguy]

Sorry, but that means you don't know how to test, either. Try this: select 1 as result from dual where 'yourstring' like 'your%%string'; According to you, the result set should have zero rows, because the input string doesn't have an ampersand in it. But in fact it does return a row with the value 1 in the result column.

[mathguy]

This is correct and it will work, but the query is modified to work around an obstacle raised by the front-end program (like SQL*Plus). The better solution, as Bob Jarvis has shown in his answer, is to tell the front-end to behave nicely - by turning off that feature in the front-end itself. We can't torture our queries so that they will avoid all the oddities of all possible front-ends; when we write a query we should only have to worry about SQL, not about other things. With that said, "good job" since the answer is actually correct.

[Juan]

Added @mathguy's comment. I thought that was only when you needed to set a different char.

[mathguy]

SQL*Plus treats anything of the form &z[...] as a substitution variable - this is not limited only to digits. The solution is the right one though.

[mathguy]

This may work for ampersand (I didn't test and I don't know off the top of my head if it will), but I do know it will not work for percent. The percent sign will be concatenated and then still interpreted as a meta-character in the LIKE condition. Escape is the right way, it just has to be done... the right way!

[Ferdinand Gaspar]

You are right for the percent and it has to use \ for it while for ampersand pipes can be used.

[Andrew]

I've never used set define off. I'll have to add that one to my toolbox.

[mathguy]

There is more to it. You can also set the marker for substitution variables to some other character (instead of &) - rather than turning that feature off. Each solution has its own uses/applications.

[W. Elbashier]

@mathguy I posted the question and i voted for this answer. It worked. I'm using Oracle SQL Developer. The set define off Why do you think it's wrong? state your reason. Thanks.

[W. Elbashier]

The set define off did help get around the "&", and the escape '\' skipped the "\".

[mathguy]

@W.Elbashier - Under the Answer you will see a notation which is also a link, it says "Edited Aug 1 at 20:43". If you click on that link, you will see what was posted initially, then you will see how it was changed after I pointed out the original version wasn't going to work. As you can see in the edited answer, the responder specifically mentions that (which is the mark of a good responder - some people will make changes and pretend they didn't, as if we can't all see the history of changes through the Edited link).