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

오라클 SQL에서 정규식 LIKE를 사용하기 위해서는 REGEXP_LIKE 함수를 사용하면 된다. 정규식을 사용하면 숫자, 특수문자 등을 쉽게 검색할 수 있고 날짜, 전화번호 등 형식이 있는 문자열도 패턴을 지정해서 검색할 수 있다. 그러나 정규식에 익숙하지 않으면 조금 어려울 수 있으니 기본적인 정규 표현식을 학습 후 예제를 보는 것을 권장한다.

 

REGEXP_LIKE("문자열", "정규표현식", "일치옵션")

일치 옵션은 사용하지 않을 경우 생략이 가능하다.

 

목차 다중 LIKE 사용하는 방법 문자열 숫자 포함 조회 방법 문자열 날짜 형식 조회 방법 문자열 특수문자 포함 조회 방법 대소문자 구분 없이 조회 방법

 

다중 LIKE 사용하는 방법

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 *
  FROM temp
 WHERE REGEXP_LIKE(ename, 'MI|AD|JA')

 

 

검색할 문자열을 파이프( | )로 구분하여 입력하면, 해당 문자열이 포함된 데이터가 조회된다.

검색할 문자열을 쿼리 인자로 넘길 수 있기 때문에 동적 쿼리가 아니라도 다중 LIKE를 사용할 수 있다.

 

 

정규식 LIKE 함수를 사용하지 않을 경우, OR 연산자와 LIKE 연산자를 사용하여 다중 LIKE를 사용할 수 있으며, 조회된 결과는 동일하다.

 

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 *
  FROM temp
 WHERE REGEXP_LIKE(ename, '^MI|^AD|^JA')

 

 

캐럿 기호( ^ )를 사용하면 문자열의 시작 부분에 해당 문자열이 존재할 경우 조회된다.

기존 LIKE에서 사용하는 방법과 아래의 내용이 유사하다고 생각하면 된다.

LIKE 'MI%' = '^MI'

LIKE '%MI' = 'MI$'

LIKE '%MI%' = 'MI'

 

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 *
  FROM temp
 WHERE REGEXP_LIKE(ename, '^A.+S$|^J.+S$')

 

 

"A" + 한 개 이상의 문자( .+ ) + "S" 또는 "J" + 한 개 이상의 문자( .+ ) + "S" 와 일치하는 데이터를 조회한다.

정규식 양쪽에 ( ^ )과 ( $ )를 붙일 경우 문자열 전체가 위의 형식에 맞아야 한다.

 

^$ 사용할 경우 : "JONES" ( O ), "KIM JONES" (X)

^$ 사용하지 않을 경우 : "JONES" ( O ), "KIM JONES" (O)

 

문자열 숫자 포함 조회 방법

WITH temp AS
(
    SELECT '123456' str FROM dual UNION ALL
    SELECT '123ABC' str FROM dual UNION ALL
    SELECT 'ABC123' str FROM dual UNION ALL
    SELECT 'ABCDEF' str FROM dual
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '[0-9]')

 

 

숫자가 포함된 데이터를 조회한다.

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

 

WITH temp AS
(
    SELECT '123456' str FROM dual UNION ALL
    SELECT '123ABC' str FROM dual UNION ALL
    SELECT 'ABC123' str FROM dual UNION ALL
    SELECT 'ABCDEF' str FROM dual
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '^[0-9]+$')

 

 

문자열 전체가 숫자인 데이터만 조회한다.

 

문자열 날짜 형식 조회 방법

WITH temp AS
(
    SELECT '2024-03-10' str FROM dual UNION ALL
    SELECT '2024/03/10' str FROM dual UNION ALL
    SELECT '20240310'   str FROM dual 
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '[0-9]{4}-[0-9]{2}-[0-9]{2}')

 

 

문자열에서 숫자(4)-숫자(2)-숫자(2) 형식의 날짜 문자열을 추출한다.

중괄호 ( { } ) 수량 자자는 앞의 문자에 대한 개수를 의미한다. ( [0-9]{4} : 숫자 4자 )

 

WITH temp AS
(
    SELECT '2024-03-10' str FROM dual UNION ALL
    SELECT '2024/03/10' str FROM dual UNION ALL
    SELECT '20240310'   str FROM dual 
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '[0-9]{4}[-/]?[0-9]{2}[-/]?[0-9]{2}')

 

 

날짜 구분자를 문자 클래스를 사용하여 여러 개를 지정할 수 있다. ( 문자 클래스 : [-/] )

물음표( ? ) 수량자를 사용하면 앞의 문자가 존재하면 체크하고, 존재하지 않으면 체크하지 않는다.

 

WITH temp AS
(
    SELECT '1924-03-10' str FROM dual UNION ALL
    SELECT '9999-03-10' str FROM dual UNION ALL
    SELECT '2024-03-10' str FROM dual 
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '(19|20)[0-9]{2}-[0-9]{2}-[0-9]{2}')

 

 

날짜 형식의 정확도를 높이기 위해서 연도 시작을 지정해 놓을 수 있다. ( 문자그룹 : (19|20) )

19 또는 20으로 시작하는 날짜 형식의 문자열만 조회한다.

 

 

위의 예제는 날짜 형식이 부정확한 데이터가 조회된다.

NOT 연산자를 사용하면 조회된 결과의 반대 결과를 조회할 수 있다.

 

문자열 특수문자 포함 조회 방법

WITH temp AS
(
    SELECT 'abc,ABC,123,가나다' str FROM dual UNION ALL
    SELECT 'abc(ABC)123가나다'  str FROM dual UNION ALL
    SELECT 'abc@ABC@123@가나다' str FROM dual UNION ALL
    SELECT 'abc ABC 123 가나다' str FROM dual UNION ALL
    SELECT 'abcABC123가나다'    str FROM dual
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '[[:punct:]]')

 

 

[:punct:] 는 특수문자를 의미하며, 공백 문자는 포함하지 않는다.

 

공백 문자는 [:space:] 을 사용하면 된다. 특수문자와 공백 문자 모두 조회할 경우

REGEXP_LIKE(str, '[[:punct:] [:space:] ]') 처럼 사용하면 된다.

 

WITH temp AS
(
    SELECT 'abc,ABC,123,가나다' str FROM dual UNION ALL
    SELECT 'abc(ABC)123가나다'  str FROM dual UNION ALL
    SELECT 'abc@ABC@123@가나다' str FROM dual UNION ALL
    SELECT 'abc ABC 123 가나다' str FROM dual UNION ALL
    SELECT 'abcABC123가나다'    str FROM dual
)

SELECT *
  FROM temp
 WHERE REGEXP_LIKE(str, '[!@#$%&,]')

 

 

조회할 특수문자 종류를 직접 지정하고 싶을 때는 위와 같이 사용하면 된다.

 

대소문자 구분 없이 조회 방법

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 *
  FROM temp
 WHERE REGEXP_LIKE(ename, 'am', 'i')

 

 

REGEXP_LIKE 함수는 기본값으로 대소문자를 구분하여 조회하며, 대소문자를 구분하지 않고 조회하고 싶을 때는 3번째 인자에 "i" 를 입력하면 된다.

일치 옵션
i	대소문자 구분 안함
c	대소문자 구분 함
n	dot(.)를 개행 문자와 일치
m	다중 행 모드 ( 앵커(^, $)에 영향 )
x	검색 패턴의 공백 문자를 무시

 

아래의 링크를 참고하면 정규 표현식을 이해하는데 조금 더 도움이 될 수 있을 것이다.

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

[Oracle] 오라클 특수문자 찾기 (정규식)

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