Oracle SQL - Select LIKE Operator

Introduction

LIKE operator in the WHERE clause works with a search pattern.

In the following example, the query returns all courses that have something to do with SQL, using the search pattern %SQL%.

select * from courses 
where description LIKE '%SQL%';

Two characters have special meaning when you use them in a string search pattern after the LIKE operator.

These two characters are commonly referred to as wildcards:

Character	Description
%	A percent sign after the LIKE operator means zero, one, or more arbitrary characters.
_	An underscore after the LIKE operator means exactly one arbitrary character.

If the LIKE operator provides insufficient search possibilities, you can use the REGEXP_LIKE function and regular expressions.

The following query returns all emp with an uppercase A as the second character in their name.

It uses the LIKE Operator with the Percent and Underscore Characters

select empno, init, ename 
from   emp 
where  ename like '_A%';

The LIKE operator has a built-in negation option: WHERE . . . NOT LIKE . . . .

Demo

SQL>
SQL> drop table courses;

Table dropped.-- w w w  .j a  v a 2  s  .com

SQL> create table courses(
  2  code        VARCHAR2(6)  primary key,
  3  description VARCHAR2(30) not null,
  4  category    CHAR(3)      not null,
  5  duration    NUMBER(2)    not null) ;
SQL> insert into courses values('SQL','Introduction to SQL',         'GEN',4);
SQL> insert into courses values('JSON','Oracle for application users','GEN',1);
SQL> insert into courses values('JAVA','Java for Oracle developers',  'BLD',4);
SQL> insert into courses values('PLS','Introduction to PL/SQL',      'BLD',1);
SQL> insert into courses values('XML','XML for Oracle developers',   'BLD',2);
SQL> insert into courses values('ERM','Data modeling with ERM',      'DSG',3);
SQL> insert into courses values('PMT','Process modeling techniques', 'DSG',1);
SQL> insert into courses values('RSD','Relational system design',    'DEF',2);
SQL> insert into courses values('PRO','Prototyping',                 'DSG',5);
SQL> insert into courses values('GEN','System generation',           'DSG',4);
SQL>
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>
SQL> select * from courses
  2  where description LIKE '%SQL%';

CODE   | DESCRIPTION                    | CAT |  DURATION
------ | ------------------------------ | --- | ---------
SQL    | Introduction to SQL            | GEN |  00004.00
PLS    | Introduction to PL/SQL         | BLD |  00001.00

SQL>
SQL> select empno, init, ename
  2  from   emp
  3  where  ename like '_A%';

    EMPNO | INIT  | ENAME
--------- | ----- | --------
 07003.00 | TF    | WARD
 07004.00 | JM    | JACK
 07934.00 | ABC   | MARY

SQL>

Oracle SQL - Select LIKE Operator

Introduction

Demo

Related Topics