sql - Escaping wildcards in LIKE

Question

How do I escape wildcards (_ and %) when using a SQL LIKE operator in Oracle?

I came to a silly issue today. I need to search for the presence of an underscore _ on a varchar column using LIKE. It doesn't work -- as expected -- since underscores are wildcards according to SQL. Here's my (simpified) code:

create table property (
  name varchar(20),
  value varchar(50)
);

insert into property (name, value) values ('port', '8120');
insert into property (name, value) values ('max_width', '90');
insert into property (name, value) values ('taxrate%', '5.20');

I tried the following queries in PostgreSQL and they return the rows I want:

select * from property where name like '%\_%'; -- should return: max_width

select * from property where name like '%\%%'; -- should return: taxrate%

Unfortunately it doesn't work in Oracle 12c. Is there a "standard" way of escaping wildcards? Or at least something that works in Oracle?

Answer 1

You can use the escape syntax

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character.

So you can do:

select * from property where name like '%\_%' escape '';

NAME                 VALUE                                             
-------------------- --------------------------------------------------
max_width            90                                                

select * from property where name like '%\%%' escape '';

NAME                 VALUE                                             
-------------------- --------------------------------------------------
taxrate%             5.20