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

REGEXP_SUBSTR 함수는 SUBSTR의 확장판으로 특정 패턴에서 주어진 문자를 추출해 내는 함수입니다.
아래 예는 주어진 문자열에서 첫 글자가 공백이 아니고 ( '[^]' ) 그 후에 'DEF'가 나오는 부분을 추출하라는 쿼리입니다.

SELECT REGEXP_SUBSTR('ABC* *DEF $GHI%KJL', '[^ ]+[DEF]') FROM DUAL; 

예제를 통해 알아보는게 제일 빠르니 예제를 알아봅시다.

1. 교수테이블에서 홈페이지 주소가 있는 교수들만 조사해서 아래의 화면처럼 나오게 출력하세요.

SELECT NAME, HPAGE, LTRIM(REGEXP_SUBSTR(HPAGE, '/([[:alnum:]]+\.?){3,4}?'), '/') "URL" 
FROM PROFESSOR 
WHERE HPAGE IS NOT NULL;

hpage 컬럼을 조회하여 'http://' 부분을 제거하고 . 으로 구분되는 필드를 최소 3개에서 4개까지 출력하라는 의미입니다.
그 후에 왼쪽 부분에 나오는 '/' 기호를 LTRIM함수로 제거하였습니다.

 

 

2. 교수테이블에서 101번 학과와 201번 학과 교수들의 이름과 메일 주소의 도메인 주소를 출력하세요.
단, 메일 주소는 @뒤에 있는 주소만 출력하세요.

SELECT NAME, EMAIL, LTRIM(REGEXP_SUBSTR(EMAIL, '@([[:alnum:]]+\.?){3,4}?'), '@') "DOMAIN" 
FROM PROFESSOR 
WHERE DEPTNO IN(101, 201);

 

3. 주어진 문자열에서 : 기호를 기준으로 3번째의 문자열을 출력하세요.
sys/oracle@racdb:1521:racdb

SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb', '[^:]+', 1, 3) RESULT
FROM DUAL;

3번째는 racdb 

2번째는 1521 

1번째는 sys/oracle@racdb 입니다.

/를 기준으로 하면 아래와 같습니다.
SELECT REGEXP_SUBSTR('sys/oracle@racdb:1521:racdb', '[^/:]+', 1, 4) RESULT
FROM DUAL;