How to handle percent (%) underscore (_) and quotes within SQL LIKE queries

Oracle Database Tips by Donald BurlesonJanuary 8, 2015

Question: I am getting unexpected results when trying to prefix wildcards (% and _) with an escape character in a LIKE clause. I also have quotes within the LIKE string, and I need SQL to treat these as literal quotes and not as string terminators. How does Oracle manage these special characters within SQL queries?

Answer: Oracle handles special characters with the ESCAPE clause, and the most common ESCAPE is for the wildcard percent sign (%), and the underscore (_). For handling quotes within a character query, you must add two quotes for each one that is desired. This will display "Larry's the Man!":

select 'Larry''s the Man!' from dual;

Most Oracle professionals use the UNIX escape character "\" backslash, but you can define any escape character that you desire in SQL*Plus.

For an example using underscores, this query wants to display all values that contain the string "_to_" (e.g. hard_to_get within a query). Here, we must escape the underscore:

set escape '\'

select stuff from mytab

where

mycol like '%\_to\_%';

Or:

select stuff from mytab

where

mycol like '%\_to\_%' escape '\';

For another example, assume that we want to seek columns that contain a percent sign (%). Here we must leave the front and % while escaping the middle percent sign:

where

mycol like '%\%%';

For more details and examples of scripts using escape characters, see these notes: