Oracle SQL - Select LIKE ESCAPE

Introduction

The following queries show two special cases: one using LIKE without wildcards and one using the % character without the LIKE operator.

select * from emp where ename like 'BLAKE' 
select * from emp where ename = 'BL%'

In the first example, we could have used the equal sign = instead of the LIKE operator to get the same results.

In the second example, the percent sign % has no special meaning, since it doesn't follow the LIKE operator.

To search for actual percent sign or underscore characters with the LIKE operator, you need to suppress the special meaning of those characters.

You can do this with the ESCAPE option of the LIKE operator.

select empno, begindate, comments 
from   history 
where  comments like '%0\%%' escape '\';

The backslash (\) suppresses the special meaning of the second percent sign in the search string.

You can pick a character other than the backslash to use as the ESCAPE character.

Demo

SQL>
SQL>-- from   w w  w .j ava 2  s.  c  o m
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> drop table history;

Table dropped.

SQL> create table history(
  2  comments   VARCHAR2(60)) ;
SQL>
SQL> insert into history values ('this is a test');
SQL> insert into history values ('salary reduction this is a test');
SQL> insert into history values ('this is a test this is a test Transfer to sales department');
SQL> insert into history values ('Not a great trainer; this is a test this is a test this is a test!');
SQL> insert into history values ('Senior sales this is a test this is a test this is a test?');
SQL> insert into history values ('Turns out to be slightly disappointing this is a test this is a test');
SQL> insert into history values ('Transfer to human resources; salary raise this is a test');
SQL> insert into history values ('Salary reduction 550 this is a test this is a test');
SQL> insert into history values ('Founder and first employee of the company');
SQL> insert into history values ('Accounting established this is a test this is a test');
SQL> insert into history values ('Project for the ACCOUNTING department this is a test');
SQL>
SQL>
SQL> select * from emp where ename like 'BLAKE' ;

    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07006.00 | BLAKE    | R     | MANAGER  |  07009.00 | 01-NOV-73 |  02850.00
 [N/A]    |  00010.00


SQL>
SQL> select * from emp where ename = 'BL%' ;

no rows selected

SQL>
SQL> select *
  2  from   history
  3  where  comments like '%0\%%' escape '\';


SQL>
SQL>

Oracle SQL - Select LIKE ESCAPE

Introduction

Demo

Related Topic