오라클에서는 문자열에서 원하는 문자를 찾을 때 INSTR 함수를 사용한다. INSTR 함수는 대소문자를 구별하여 문자를 검색하며, 여러 개의 문자를 찾을 때는 OR 연산자를 사용하면 된다. 해당 문자를 한번 찾으면 더 이상 검색을 하지 않기 때문에 필요에 따라서 뒤(마지막)에서 부터 검색을 할 수도 있다.
|
INSTR ( [문자열], [찾을 문자 값], [찾기를 시작할 위치(1,-1)], [찾은 결과의 순번(1...n)] ) |
기본 사용법
SELECT INSTR('Oracle Database', 'Database') AS result1
, INSTR('Oracle Database', 'Server') AS result2
FROM dual
 |
INSTR 함수는 문자열에서 문자를 찾으면 문자의 시작 위치를 반환한다. 문자를 찾지 못하면 "0"을 반환한다.
"Server"라는 문자열은 "Oracle Database"에 포함되지 않기 때문에 "0"을 반환한다.
앞에서부터 검색
SELECT INSTR('Oracle Database 12c Release', 'as', 1) AS result1
, INSTR('Oracle Database 12c Release', 'as') AS result2
FROM dual
 |
문자열의 처음부터 검색할 때는 세 번째 파라미터("1")는 생략해도 동일한 결과가 반환된다.
SELECT INSTR('Oracle Database 12c Release', 'as', 1, 2) AS result1
FROM dual
 |
INSTR 함수의 네 번째 파라미터는 문자를 찾은 결과 순번이며, 기본값은 "1"인데 필요한 순번을 부여하면 해당 순번의 위치가 반환된다.
SELECT INSTR('Oracle Database 12c Release', 'as', 15) AS result1
FROM dual
 |
세 번째 파라미터에 검색을 시작할 위치를 지정할 수가 있다.
뒤에서부터 검색
SELECT INSTR('Oracle Database 12c Release', 'as', -1) AS result1
FROM dual
 |
검색을 시작할 위치에 마이너스("-") 기호를 붙이면 뒤에서부터 검색을 시작한다.
SELECT INSTR('Oracle Database 12c Release', 'as', -1, 2) AS result1
FROM dual
 |
네 번째 위치에 문자를 찾은 순번을 부여하면 부여된 순번의 찾은 위치가 반환된다.
SELECT INSTR('Oracle Database 12c Release', 'as', -4) AS result1
FROM dual
 |
뒤에서 네 번째 문자부터 검색을 시작한다.
대소문자 구분 없이 검색
SELECT INSTR('Oracle Database 12c Release', 'DATABASE') AS result1
, INSTR(UPPER('Oracle Database 12c Release'), 'DATABASE') AS result2
FROM dual
 |
INSTR 함수는 대문자, 소문자를 구별하여 검색을 하기 때문에 UPPER 함수 또는 LOWER 함수를 사용하여 변환 후 대소문자 구분 없이 검색을 할 수 있다.
문자열 자르기
WITH temp AS (
SELECT 'Oracle Database 12c Release' AS oracle_ver FROM dual
)
SELECT SUBSTR(oracle_ver, INSTR(oracle_ver, 'Database')) AS result1
, SUBSTR(oracle_ver, INSTR(oracle_ver, 'Database'), 4) AS result2
FROM temp
 |
SUBSTR 함수와 함께 문자열을 자를 때 많이 사용한다.
WHERE 절에 사용
WITH temp AS (
SELECT 'Oracle Database 12c Release' AS oracle_ver FROM dual
)
SELECT *
FROM temp
WHERE INSTR(oracle_ver, '12c') > 0
 |
WHERE 절에서 조건을 부여할 때 사용할 수 있다.
WHERE oracle_ver LIKE '%12c%와 동일한 결과를 반환한다.
INSTR 여러 개 사용하기
WITH temp AS (
SELECT 'Oracle Database 10g Release' AS oracle_ver FROM dual UNION ALL
SELECT 'Oracle Database 11g Release' AS oracle_ver FROM dual UNION ALL
SELECT 'Oracle Database 12c Release' AS oracle_ver FROM dual
)
SELECT *
FROM temp
WHERE (INSTR(oracle_ver, '10g') > 0
OR INSTR(oracle_ver, '11g') > 0
OR INSTR(oracle_ver, '12c') > 0)
 |
여러 개의 문자를 검색할 때에는 OR 연산자를 사용한다.
IN 대신 사용하는 방법
WITH dept AS (
SELECT 10 AS deptno, 'ACCOUNTING' AS dname, 'NEW YORK' AS loc FROM dual UNION ALL
SELECT 20 AS deptno, 'RESEARCH' AS dname, 'DALLAS' AS loc FROM dual UNION ALL
SELECT 30 AS deptno, 'SALES' AS dname, 'CHICAGO' AS loc FROM dual UNION ALL
SELECT 40 AS deptno, 'OPERATIONS' AS dname, 'BOSTON' AS loc FROM dual
)
SELECT *
FROM dept
WHERE INSTR('20|30|40', deptno) > 0
 |
여러개의 코드를 조회할 때에는 IN을 사용하는데, IN을 사용하면 쿼리 문의 변수에 값을 전달하기 힘든 경우가 많다. 코드의 길이가 일정한 경우 파이프("|") 또는 특수문자로 구분하여 조회할 수 있다.
WHERE deptno IN ('20', '30', 40')과 동일한 결과를 반환한다. (실무에서 자주 사용)
WITH dept AS (
SELECT 10 AS deptno, 'ACCOUNTING' AS dname, 'NEW YORK' AS loc FROM dual UNION ALL
SELECT 20 AS deptno, 'RESEARCH' AS dname, 'DALLAS' AS loc FROM dual UNION ALL
SELECT 30 AS deptno, 'SALES' AS dname, 'CHICAGO' AS loc FROM dual UNION ALL
SELECT 40 AS deptno, 'OPERATIONS' AS dname, 'BOSTON' AS loc FROM dual
)
SELECT *
FROM dept
WHERE INSTR('|RESEARCH|SALES|', '|' || dname || '|') > 0
 |
값의 길이가 일정하지 않은 경우 정확한 값을 조회하기 위해서는 특수문자를 앞뒤에 추가로 붙여줘야 한다.