[Oracle] 오라클 REGEXP_INSTR 함수 사용법

오라클 SQL에서 정규식을 사용하여 문자열의 위치를 찾으려면 REGEXP_INSTR 함수를 사용하면 된다. 기본 함수 INSTR 함수를 사용하면 단순 문자열의 위치만 찾을 수 있지만, REGEXP_INSTR 함수를 사용하면 숫자, 특수문자, 한글 등 문자열 형식 또는 정규식 패턴을 사용하여 위치를 찾을 수 있다.

 

REGEXP_INSTR("문자열", "정규표현식","시작위치","찾은횟수","반환옵션","일치옵션")

시작위치부터는 사용하지 않을 경우 생략할 수 있다.

 

목차 기본 사용법 숫자, 특수문자, 영문자, 한글 위치 찾기 정규식을 사용하여 위치 찾기 기타 옵션 사용법

 

기본 사용법

WITH temp AS
(
    SELECT 'MILLER' ename FROM dual UNION ALL
    SELECT 'JONES'  ename FROM dual UNION ALL
    SELECT 'SMITH'  ename FROM dual UNION ALL
    SELECT 'ADAMS'  ename FROM dual UNION ALL
    SELECT 'JAMES'  ename FROM dual
)

SELECT ename
     , REGEXP_INSTR(ename, 'MI') AS result
  FROM temp

 

 

단순 문자열의 위치를 찾을 때는 두 번째 인자에 문자열을 입력하면 되며, 찾은 문자열의 시작위치를 반환한다.

단순 문자열 찾을 때는 INSTR 함수와 동일한 결과를 반환한다.

 

여러 개의 문자열을 동시에 찾는 방법

WITH temp AS
(
    SELECT 'MILLER' ename FROM dual UNION ALL
    SELECT 'JONES'  ename FROM dual UNION ALL
    SELECT 'SMITH'  ename FROM dual UNION ALL
    SELECT 'ADAMS'  ename FROM dual UNION ALL
    SELECT 'JAMES'  ename FROM dual
)

SELECT ename
     , REGEXP_INSTR(ename, 'MI|AD|AM') AS result
  FROM temp

 

 

여러 개 문자열의 위치를 동시에 찾을 때는, 찾을 문자열을 파이프( | )로 연결하여 입력하면 된다. 입력된 문자열 중 해당되는 문자열이 있을 경우 첫 번째 찾은 문자열의 위치를 반환한다.

 

대소문자 구분 없이 위치 찾기

WITH temp AS
(
    SELECT 'MILLER' ename FROM dual UNION ALL
    SELECT 'JONES'  ename FROM dual UNION ALL
    SELECT 'SMITH'  ename FROM dual UNION ALL
    SELECT 'ADAMS'  ename FROM dual UNION ALL
    SELECT 'JAMES'  ename FROM dual
)

SELECT ename
     , REGEXP_INSTR(ename, 'mi')               AS result1
     , REGEXP_INSTR(ename, 'mi', 1, 1, 0, 'i') AS result2
  FROM temp

 

 

REGEXP_INSTR 함수의 마지막 인자에 "i"를 입력하면, 대소문자 구분 없이 영문자를 찾는다.

 

숫자, 특수문자, 영문자, 한글 위치 찾기

문자열에서 숫자 위치 자기

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '\d')          AS result1
     , REGEXP_INSTR('abc@ABC@123@가나다', '[0-9]')       AS result2
     , REGEXP_INSTR('abc@ABC@123@가나다', '[[:digit:]]') AS result3
  FROM dual

 

 

\d, [0-9], [[:digit:]]는 모두 숫자를 의미하며, 자신에게 익숙한 형식을 사용하면 된다.

문자열에서 첫 번째 찾은 숫자의 위치를 반환한다.

 

문자열에서 특수문자 위치 찾기

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '[[:punct:]]') AS result1
     , REGEXP_INSTR('abc,ABC,123,가나다', '[[:punct:]]') AS result2
     , REGEXP_INSTR('abc/ABC/123/가나다', '[[:punct:]]') AS result3
  FROM dual

 

 

[[:punct:]]는 모든 특수문자를 의미하며, 스페이스 문자는 포함되지 않는다.

특정 특수문자만 찾을 경우 REGEXP_INSTR('문자열', '[!@#$%&,]')와 같이 필요한 특수문자만 찾으면 된다.

 

문자열에서 영문자 위치 찾기

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '[a-zA-Z]') AS result1
     , REGEXP_INSTR('abc@ABC@123@가나다', '[a-z]')    AS result2
     , REGEXP_INSTR('abc@ABC@123@가나다', '[A-Z]')    AS result3
  FROM dual

 

 

[[:alpha:]]는 모든 영문자, [a-z]는 소문자, [A-Z]는 대문자를 의미한다.

[[:alpha:]]와 [a-zA-Z]는 동일한 모든 영문자를 의미한다.

 

문자열에서 한글 위치 찾기

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '[가-힣]') AS result
  FROM dual

 

 

[가-힣]는 한글을 의미한다. 문자열에서 찾은 첫 번째 한글의 위치를 반환한다.

 

정규식을 사용하여 위치 찾기

WITH temp AS
(
    SELECT 'MILLER' ename FROM dual UNION ALL
    SELECT 'JONES'  ename FROM dual UNION ALL
    SELECT 'SMITH'  ename FROM dual UNION ALL
    SELECT 'ADAMS'  ename FROM dual UNION ALL
    SELECT 'JAMES'  ename FROM dual
)

SELECT ename
     , REGEXP_INSTR(ename, '[MN]ES')       AS result1
     , REGEXP_INSTR(ename, 'A[A-Z]{1,2}S') AS result2
  FROM temp

 

 

대괄호는 문자 클래스( [ ] )이며 문자 클래스의 문자는 각각 개별의 문자로 인식한다. 문자 클래스에 범위( - )가 있을 경우 문자의 시작부터 끝까지 해당된다. [ABCD]와 [A-D]는 동일하다고 생각하면 된다.

 

중괄호( { } )는 바로 앞의 문자 개수를 의미한다. {1} 하나의 문자, {2} 두 개의 문자, {1, 2} 1~2개의 문자, {2,} 2개 이상의 문자 등 문자개수를 지정할 수 있다.

 

SELECT REGEXP_INSTR('abc(ABC)(123)가나다', '\([0-9]+\)')       AS result1
     , REGEXP_INSTR('abc(ABC)(123)가나다', '\([[:alpha:]]+\)') AS result2
     , REGEXP_INSTR('abc(ABC)(123)가나다', '\(.+\)')           AS result3
  FROM dual

 

 

괄호( ( ) )는 정규식에서 사용하는 메타문자 이기 때문에, 단순 문자로 사용하기 위해서는 역슬래시( \ )를 함께 사용해야 한다. 위의 예제는 괄호와 문자가 함께 있는 문자열을 찾는 예제이다.

 

더하기( + ) 기호는 앞의 문자가 1개 이상 존재해야 한다는 의미이다. 점( . )은 임의의 한 문자를 의미한다.

 

SELECT REGEXP_INSTR('Oracle 21c 2020-12-08 Release', '\d{4}-\d{2}-\d{2}') AS result
  FROM dual

 

 

\d는 숫자를 의미하며, 날짜 형식의 문자열을 찾는 예제이다.

 

기타 옵션 사용법

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '@')    AS result1
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 5) AS result2
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 9) AS result3
  FROM dual

 

 

REGEXP_INSTR 함수에서 3번째 인자는 문자열의 검색 시작 위치는 의미한다.

 

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '@')       AS result1
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 1, 2) AS result2
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 1, 3) AS result3
  FROM dual

 

 

REGEXP_INSTR 함수에서 4번째 인자는 문자열을 찾은 횟수에 해당하는 위치를 반환하라는 의미이다.

 

SELECT REGEXP_INSTR('abc@ABC@123@가나다', '@')          AS result1
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 1, 1, 0) AS result2
     , REGEXP_INSTR('abc@ABC@123@가나다', '@', 1, 1, 1) AS result3
  FROM dual

 

 

REGEXP_INSTR 함수에서 5번째 인자는 "0" 또는 "1"을 입력할 수 있으며, "1"을 입력할 경우 찾은 문자열의 다음 문자열 시작 위치를 반환한다.

 

[Oracle] 정규식 사용법 쉽게 설명 (REGEXP)

[Oracle] 오라클 REGEXP_SUBSTR 함수 사용법

[Oracle] 오라클 REGEXP_LIKE 함수 사용법

[Oracle] 오라클 REGEXP_REPLACE 함수 사용법