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?

Answer 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';