Oracle Escape Characters in SQL

When doing SQL in Oracle where you want to search for something containing a string, you can use the like command.

 Select * from table1 where field1 like ‘somevalue’;

This will return rows with field1 = somevalue, not exactly useful.  But you can use the wildcard ‘%’ so

 Select * from table1 where field1 like ‘%somevalue%’;

This will return rows with field1 containing the string ‘somevalue’ in any form.  There is another wildcard ‘_’ which will match any character in that position, so

Select * from tabe1 where field1 like ‘_omevalue’;

This will return rows with field1 = aomevalue, bomevalue, comevalue, domevalue, …., zomevalue

If you want to use the the literal of % and _ then you will have to put an escape character in front of it, and tell Oracle what the escape character is:

Select * from table1 where field1 like ‘lookfor\%’ escape ‘\’

This will return rows where field1 = ‘lookfor%’