오라클 SQL에서 LIKE연산자
는 자주 사용하는 구문 중 하나입니다.
문자열에서 와일드카드(%)
를 사용하여 원하는 문자가 포함된 자료를 쉽게 검색할 수 있습니다.
단순 검색 외에도
1. 대문자 소문자 구분 없이 검색
2. NOT 연산자 사용
3. 여러 개의 문자를 검색
4. 언더바(_)를 사용하여 자릿수 검색 등
다양한 사용법이 있습니다.
SELECT *
FROM emp
WHERE ename LIKE 'SC%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | 20 |
SELECT *
FROM emp
WHERE ename LIKE '%ER'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7788 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | 0 | 10 |
SELECT *
FROM emp
WHERE ename LIKE '%AM%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 |
"AM"이 이름은 앞, 뒤, 중간 어딘가에 존재하면 검색한다.
SELECT *
FROM emp
WHERE ename LIKE '%M%I%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | 20 | |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | 10 |
"M"과 "I"가 동시에 존재하는 이름을 검색한다. 단, "M"다음에 "I"가 존재해야 한다.
SELECT *
FROM emp
WHERE LOWER(job) LIKE 'sales%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7499 | ALLEN | SALEMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
7654 | MARTIN | SALESMAN | 7698 | 1982-09-28 00:00:00 | 1250 | 1400 | 30 |
7844 | TURNER | SALESMAN | 7698 | 1982-09-08 00:00:00 | 1500 | 0 | 30 |
LIKE는 대소문자를 구분하여 검색하기 때문에, 대소문자 구분 없이 검색하기 위해서는 UPPER
, LOWER
함수를 사용하여 컬럼의 값을 치환 후 검색해야 한다.
WHERE LOWER(job) LIKE 'sales%'
WHERE LOWER(job) LIKE LOWER('Sales%')
WHERE UPPER(job) LIKE 'SALES%'
WHERE UPPER(job) LIKE UPPER('Sales%')
SELECT *
FROM emp
WHERE deptno = 30
AND job NOT LIKE 'SALES%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 |
LIKE는 해당 문자를 포함한 자료를 검색하지만, NOT LIKE
를 사용하면 해당 문자를 포함하지 않는 데이터를 조회할 수 있다.
SELECT *
FROM emp
WHERE deptno IN (20, 30)
AND (
job LIKE 'SALES%' OR
job LIKE 'MAN%' OR
job LIKE 'ANAL%'
)
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 | |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | 20 | |
7902 | FORD | ANALYST | 7566 | 1982-12-03 00:00:00 | 3000 | 20 | |
7499 | ALLEN | SALEMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
7654 | MARTIN | SALESMAN | 7698 | 1982-09-28 00:00:00 | 1250 | 1400 | 30 |
7844 | TURNER | SALESMAN | 7698 | 1982-09-08 00:00:00 | 1500 | 0 | 30 |
LIKE를 사용하여 여러 개의 문자를 검색하기 위해서는 OR 연산자
를 사용하여 여러 개의 LIKE 조건을 부여해야 한다. 조금 더 간편하게 사용하기 위해서는 오라클 10g부터 사용 가능한 REGEXP_LIKE
정규식 함수를 사용하면 된다.
✔알아두면 유용한 팁
특정 조건을 전체 조회 또는 특정 코드만 조회시 LIKE 연산자를 사용하면 편리함
(단 컬럼 값의 길이(자리수)가 일정해야함)
in_deptno 변수에 NULL을 전달하면 전체조회, 코드(10, 20, 30)를 전달하면 해당 코드만 조회
WHERE deptno LIKE :in_deptno || '%'
SELECT *
FROM emp
WHERE ename LIKE '_____' -- 언더바(_) 다섯 개
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | 30 | |
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | 20 | |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | 20 | |
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 3000 | 20 | |
7499 | ALLEN | SALEMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 |
문자열의 자릿수
를 결정하여 검색이 가능하다. 위의 예제는 다섯 자리의 이름만 검색한다.
SELECT *
FROM emp
WHERE ename LIKE '____S'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | 20 | |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 | |
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | 30 |
이름의 앞 네자리는 임의 문자가 존재(자릿수 중요)하고, 마지막 다섯 번째는 "S"가 존재하는 이름만 검색한다.
SELECT *
FROM emp
WHERE ename LIKE '__A%'
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
---|---|---|---|---|---|---|---|
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | 30 | |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | 30 | |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | 20 |
이름 앞의 두 자리는 임의 문자가 존재하고, 세 번째 자리는 "A"이며 이후는 어떠한 문자가 존재해도 상관없이 검색된다.
언더바(_)가 존재하는 데이터를 검색할 때는 ESCAPE 문자를 언더바에 붙여서 사용해야 한다.
LIKE '%\n%'ESCAPE'\'
LIKE '%#_%'ESCAPE'#'
LIKE '%$_%'ESCAPE'$'
(끝)