오라클 SQL과 PL/SQL ( 정규식 / REGEXP_LIKE )

정규식(Regular Expression) 함수로 다양한 조건 조회하기
SQL 문장에서도 유닉스에서 사용하는 정규식을 사용하여 다양한 검색을 할 수 있습니다.
정규식이란 유닉스에서 검색을 할 때 주로 사용되는 기술로 다양한 메타문자들을 사용하여 검색 방법을 확장하는 것을 말합니다.

 

정규식함수를 잘 활용하면 아주 많은 일들을 할 수 있습니다. 하지만 조금 어려울 수 있으니 어렵더라도 연습을 해야합니다.

 

1. REGEXP_LIKE 함수 : LIKE 함수처럼 특정 패턴과 매칭되는 결과를 검색해내는 함수

 

예제 1. 영문자가 들어있는 행만 출력하기

 

SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z]');

 

SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]');

 

SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-zA-Z]');

 

예제 2. 소문자로 시작하고 공백을 포함하는 경우 찾기

 

소문자로 시작하고 뒤에 공백이 있는 모든행 찾기
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z] ');

 

소문자로 시작하고 공백이 1칸 있고 숫자로 끝나는 행 찾기
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-z] [0-9]');

 

 

공백이있는 데이터를 모두 찾기
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:space:]]');

 

예제 3. 연속적인 글자 수 지정하기

 

대문자가 연속적으로 2글자 이상 오는 경우 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]{2}');

대문자가 연속적으로 4글자 이상 오는 경우 출력 ( 4글자 이상 오는 경우는 없어서 출력 안됨)
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]{4}');

 

대문자와 숫자가 각각 3글자가 나오는 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z][0-9]{3}');

 

대문자와 숫자가 나오되 숫자가 먼저나오는 행 출력 ( 조건이 맞지않아 출력안됨 )

대문자가 나오는 모든 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:upper:]]');

or

SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]');

 

예제 4. 특정 위치를 지정하여 출력

 

첫 시작을 대문자나 소문자로 하는 모든 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[A-Za-z]');

 

첫 시작을 숫자나 대문자로 하는 모든 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[0-9A-Z]');

 

첫 시작을 소문자로 시작하거나 또는 숫자로 시작하는 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[a-z]|^[0-9]');

 

문제 1.

STUDENT테이블에서 학생 ID중에서 첫글자가 M으로 시작하고 두번째 글자가 A나 O가 오는 ID를 이름과 함께 출력

SELECT NAME, ID
FROM STUDENT
WHERE REGEXP_LIKE(ID, '^M(a|o)'); 

 

$문자를 사용하여 소문자로 끝나는 행 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[a-zA-Z]$') 

or

SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:alpha:]]$') 

 

^(캐럿) 문자가 대괄호 안에 들어갈 경우에는 대괄호 안의 문자가 아닌 다른 것만 출력하라는 의미가 됩니다 (not 조건)

 

소문자로 시작하지 않는 행을 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^a-z]') 

숫자나 소문자로 시작하지 않는 행을 출력
SELECT * FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^0-9a-z]') 

 

소문자가 아닌 모든 행 제거 (NOT)
SELECT * FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[a-z]') 

 

문제 2.

STUDENT 테이블에서 지역번호가 2자리이고 다음 국번이 연속적으로 4자리 숫자가 나오는 값을 출력

 

SELECT NAME, TEL FROM STUDENT 
WHERE REGEXP_LIKE(TEL, '^[0-9]{2}\)[0-9]{4}') 

 

문제 3.

STUDENT 테이블의 학생 ID중 4번째 자리에 소문자 r이 있는 행을 출력하기

SELECT NAME, ID FROM STUDENT 
WHERE REGEXP_LIKE(ID, '^...r.') 

문제 4.

T_REG2 테이블에서 IP 주소가 10.10.10.1 인 행만 출력하기

SELECT * FROM T_REG2
WHERE REGEXP_LIKE(IP, '^[10]{2}\.[10]{2}\.[10]{2}')

만약 172.16.168.xxx 의 IP를 출력하고 싶을 경우는
SELECT * FROM T_REG2
WHERE REGEXP_LIKE(IP, '^[172]{3}\.[16]{2}\.[168]{3}') 

 

예제 5. 특정 조건을 제외한 결과를 출력하기
NOT : ~ 가 아니다라는 뜻으로 사용합니다.

 

영문자를 포함하지 않는 모든 행 출력
SELECT * FROM T_REG 
WHERE NOT REGEXP_LIKE(TEXT, '[a-zA-Z]')

 

숫자를 포함하지 않는 모든 행 출력
SELECT * FROM T_REG WHERE NOT REGEXP_LIKE(TEXT, '[0-9]');

 

특수문자 찾기  < ! 들어간 행 찾기 >

SELECT * FROM T_REG 
WHERE REGEXP_LIKE(TEXT, '!');

 

SQL 에서 ? 나 * 같은 기호는 SQL에서 모든것이라는 뜻을 가진 메타케릭터 문자기 때문에 그 문자를 바로 적을 경우는 모든 행이 출력됩니다.

그래서 ? 나 * 같은 문자를 오라클에게 ? 나 * 다 라고 알려주기 위해서는 \ 문자를 같이 사용합니다.
SELECT * FROM T_REG 
WHERE REGEXP_LIKE(TEXT, '\*');