Oracle 정규표현식 REGEXP_LIKE 예제

■ 참고

■ 문자 클래스

[:digit:] : 숫자
[:punct:] : 특수문자
[:alpha:] : 알파벳

오라클 쿼리에서 사용하는 정규표현식 검색 기능을 예제 중심으로 정리한다. 테스트용 임시 테이블은 아래와 같다.

WITH TBL AS (
    SELECT ' ' AS COL FROM DUAL UNION ALL
    SELECT 'a' AS COL FROM DUAL UNION ALL
    SELECT 'aa' AS COL FROM DUAL UNION ALL
    SELECT 'aaa' AS COL FROM DUAL UNION ALL
    SELECT 'B' AS COL FROM DUAL UNION ALL
    SELECT 'BB' AS COL FROM DUAL UNION ALL
    SELECT 'BBB' AS COL FROM DUAL UNION ALL
    SELECT 'ab' AS COL FROM DUAL UNION ALL
    SELECT 'aabb' AS COL FROM DUAL UNION ALL
    SELECT 'xaa acbbb' AS COL FROM DUAL
)

■ 일반 LIKE 검색에서 쓰이는 ‘%’처럼 모든 문자를 매치하는 방법은 ‘.*’를 사용한다. 문자 사이에 사용된 ‘.*’는 모든 문자를 의미한다. ‘a.*c.*B’는 문자 a,c,B가 순서대로 있는 모든 문자열을 선택하고 세번째 파라메터 ‘i’는 대소문자 구분을 하지 않는다는 의미이다.

SELECT COL FROM TBL
  WHERE REGEXP_LIKE (COL,'a.*c.*B','i')

■ 세번째 파라메터에서 ‘i’를 사용했으므로 대소문자 구분없이 a,b 또는 공백이 있는 모든 문자열을 구하는 조건식이다.

SELECT COL FROM TBL
  WHERE REGEXP_LIKE(COL,'[ab ]','i')

■ 하나라도 숫자가 아닌것이 있으면 선택, REGEXP_LIKE(ID, ‘[^[:digit:]]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '[^[:digit:]]');

■ 하나라도 숫자를 포함하는 문자열, REGEXP_LIKE(ID, ‘[[:digit:]]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '[[:digit:]]');

■ 알파벳이 아닌 문자를 포함하는 문자열, REGEXP_LIKE(ID, ‘[^[:alpha:]]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '[^[:alpha:]]') ;

■ ‘1’뒤에 ‘q’를 포함하는 문자열, REGEXP_LIKE(ID, ‘1[q]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '1[q]');

■ ‘1’뒤에 ‘2’이나 ‘q’를 포함하는 문자열, REGEXP_LIKE(ID, ‘1[2q]’) 또는 REGEXP_LIKE(ID, ‘1[q2]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '1[2q]');

■ ‘1qw’뒤에 ‘2’가 없는 문자열, REGEXP_LIKE(ID, ‘1qw[^2]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '1qw[^2]') ;

■ ‘1’뒤에 ‘2’가 있는 문자열, ‘1’과 ‘2’사이에 문자가 있거나 없거나 상관없음,
REGEXP_LIKE(ID, ‘[1]?[2]’) 또는 REGEXP_LIKE(ID, ‘[1]*[2]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '[1]?[2]') ;

■ 문자로만 조합된 문자열, REGEXP_LIKE(ID, ‘^[^0-9]*$’) 또는 NOT REGEXP_LIKE(ID, ‘[0-9]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '^[^0-9]*$');

■ 숫자로만 조합된 문자열 , REGEXP_LIKE(ID, ‘^[0-9]*$’) 또는 NOT REGEXP_LIKE(ID, ‘[^0-9]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL    
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL    
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL    
    SELECT 'e*'     AS ID FROM DUAL UNION ALL    
    SELECT '@!'     AS ID FROM DUAL UNION ALL    
    SELECT '123'    AS ID FROM DUAL)
SELECT ID FROM BASE
 WHERE REGEXP_LIKE(ID, '^[0-9]*$');

■ 영문 대문자, 소문자, 숫자로만 조합된 문자열, REGEXP_LIKE(ID, ‘^[A-Za-z0-9]*$’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(ID, '^[A-Za-z0-9]*$')

■ 알파벳만 존재하는 문자열, REGEXP_LIKE(ID, ‘^[[:alpha:]]*$’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(ID, '^[[:alpha:]]*$');

■ 특수문자를 포함하는 문자열, REGEXP_LIKE(ID, ‘[[:punct:]]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(ID, '[[:punct:]]');

■ 특수문자만 존재하는 문자열, REGEXP_LIKE(ID, ‘^[[:punct:]]*$’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(ID, '^[[:punct:]]*$');

■ 한글을 포함하는 문자열, REGEXP_LIKE(id, ‘[가-힝]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '[가-힝]');

■ 한글만 존재하는 문자열, REGEXP_LIKE(id, ‘^[가-힝]*$’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '[가-힝]');

■ 숫자가 연속으로 2개이상인 문자열, REGEXP_LIKE(id, ‘[0-9]{2}’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '[0-9]{2}');

■ 숫자나 소문자로 시작하는 문자열, REGEXP_LIKE(id, ‘^[0-9]|^[a-z]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!박'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '^[0-9]|^[a-z]');

■ 숫자로 시작하지 않는 문자열, REGEXP_LIKE(id, ‘^[^0-9]’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!박'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '^[^0-9]');

■ 숫자나 소문자로 끝나는 문자열, REGEXP_LIKE(id, ‘[0-9]$|[a-z]$’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!박'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '[0-9]$|[a-z]$');

■ 특수문자 ‘!’나 ‘*’가 들어가는 행, REGEXP_LIKE(id, ‘\!|\*’)

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!박'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '\!|\*') ;

오라클 시작, 끝 날짜 범위내 날짜 전부 출력 2021년 6월 15일
Oracle 정규표현식 REGEXP_REPLACE 예제 2019년 3월 11일
Oracle 정규표현식 REGEXP_LIKE 예제 2019년 3월 8일
오라클 rowid 2019년 1월 7일
오라클 MERGE INTO 2019년 1월 7일
오라클에서 과거 최근 월요일 구하기 2019년 1월 3일
오라클 cube 예제 2018년 12월 13일
오라클 rollup 예제 2018년 12월 13일
하나의 행을 여러행으로 복제, LEVEL… CONNECT BY 2017년 12월 26일
오라클 특정 년도의 월 리스트 2017년 11월 20일
오라클 가로를 세로로, LISTAGG… WITH GROUP… 2017년 11월 16일
mybatis, oracle procedure(오라클 커서)를 통한 데이터 조회 2017년 11월 6일
oracle 트리구조 부모 like 검색(역검색) 2017년 10월 17일
오라클 버전에 관계 없이 특정 문자 개수 확인 팁 2017년 10월 11일
오라클 동적 멀티 뷰 생성 및 활용 예제 2017년 8월 6일
oracle 날짜 관련 쿼리 모음 2017년 8월 6일
오라클 WHERE절에 CASE WHEN THEN ELSE END문 예제 2017년 8월 6일
쿼리로 오라클 버전 확인 2017년 8월 6일
Toad에서 오라클 프로시져 컴파일 경고 메세지 확인 2017년 7월 29일
오라클 테이블 컬럼 추가,수정,삭제 2015년 1월 2일
오라클 테이블 컬럼 주석 추가 2015년 1월 2일
오라클 테이블 생성 스크립트 2014년 12월 31일
오라클 테이블스페이스 생성, 사용자 추가 2014년 12월 31일