■ 참고 ■ 문자 클래스 오라클 쿼리에서 사용하는 정규표현식 검색 기능을 예제 중심으로 정리한다. 테스트용 임시 테이블은 아래와 같다. ■ 일반 LIKE 검색에서 쓰이는 ‘%’처럼 모든 문자를 매치하는 방법은 ‘.*’를 사용한다. 문자 사이에 사용된 ‘.*’는 모든 문자를 의미한다. ‘a.*c.*B’는 문자 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’사이에 문자가 있거나 없거나 상관없음, 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, '\!|\*') ; |