Search This Blog

Wednesday, April 27, 2016

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

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.

--Retrive columns name ends with underscore for owner 'XYZ'
select  *
from all_tab_columns
where owner = 'XYZ'
and column_name like '%\_' escape '\'
order by table_name , column_name
;