Oracle regexp_substr 예제 - Oracle regexp_substr yeje

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;

o REGEXP_SUBSTR

| 문법

regexp_substr::=

| 목적

문자열에서 지정한 패턴 조건(정규표현식, 정규식)을 만족하는 부분의 문자열을 반환한다.

REGEXP_SUBSTR함수는 정규 표현 패턴에 대하여 문자열을 검색할 수 있도록 REPLACE함수의 기능을 확장한 것이다. REGEXP_INSTR과 유사하나, substring의 위치를 반환하는 것이 아니라, substring 자체를 반환한다. 이 함수는 만약 일치 문자열의 내용은 필요하고, 소스 문자열 내에서 위치는 필요 없는 경우에 유용하다. 함수는 source_string으로써 같은 문자 세트 안에서 VARCHAR2 또는 CLOB 데이터와 같은 문자열을 반환한다.

이 함수는 POSIX 정규 표현 규격과 유니코드 정규 표현 가이드라인을 따른다.

• source_string는 검색 값으로써 전달되는 문자 표현이다. 일반적으로 문자열이고, CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB데이터형 중에 하나이다.
• pattern은 정규 표현이다. 일반적으로 텍스트 문자이고, CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터형 중에 하나이다. 512Byte를 지정할 수 있다. 만약 pattern의 데이터형이 source_string 데이터형과 다르면, 오라클 데이터 베이스는 pattern을 source_string의 데이터형으로 변환한다. pattern에서 지정할 수 있는 연산자의 리스트는  Appendix C, " Oracle Regular Expression Support" 참조.
• position은 오라클이 검색을 시작하는 source_string의 문자 위치를 나타내는 양의 정수이다. 기본값은 1이며, 오라클이 source_string의 첫 번째 문자에서 검색을 시작하는 것을 의미한다.
• occurrence은 source_string에서 pattern의 발생을 나타내는 양의 정수이다. 기본값은 1이고, 오라클이 pattern의 처음 발생하는 것을 검색하는 것을 의미한다.
• match_parameter은 함수의 기본적 일치하는 행동을 변경하기 위한 텍스트 문자이다. match_parameter에 대한 다음 값의 하나 이상을 지정할 수 있다.
  • 'i'는 대소문자를 구별하지 않는다.
  • 'c'는 대소문자를 구별한다.
  • 'n'은 새 줄 문자와 일치하는 match-any-character문자인 period(.)을 허용한다. 만약 이 파라미터를 생략하면, 피어리드는 새 줄 문자와 일치하지 않는다.
  • 'm'는 다중 라인과 같은 소스 문자열을 처리한다. 오라클은 소스 문자에 임의의 라인에 선두와 끝으로써 ^과 $을 해석한다. 만약 이 파라미터를 생략하면, 오라클은 단일행으로써 소스 문자열을 처리한다. 만약 복수의 모순된 값을 지정하면, 오라클은 마지막 값을 이용한다. 예를 들어, 만약 'ic'를 지정하면, 오라클은 대소문자를 구별하는 검색을 한다. 만약 위에서 보이는 것과 다른 문자를 지정하면, 오라클은 에러를 발생한다. 만약 match_parameter를 생략하면,
  • 기본적 대소문자 구별은 NLS_SORT 파라미터의 값에 의해 정의된다.
  • period(.)은 새 줄 문자와 일치하지 않는다.
  • 소스 문자열은 단일행으로써 취급된다.
    참 조 : SUBSTR and REGEXP_INSTR 
  • 도서 : Oracle Regular Expressions 참조

| 참고

• REGEXP_SUBSTR Oracle Function 프로그래밍 비교 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]

| 예제

다음 예제는 string를 조사하여, 콤마에 의해 단락 지어진 처음 substring을 검색한다. 오라클 데이터 베이스는 콤마에 의해 따르는 콤마 문자가 아닌 1개 이상의 문자에 따르는 콤마를 검색한다. 오라클은 앞뒤 콤마를 포함한 substring을 반환한다.

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
                     ',[^,]+,') "REGEXPR_SUBSTR"
FROM   DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,

다음 예제는 string을 조사하여, 하나 이상의 영숫자의 substring과 옵션으로 period(.)를 따르는 http://를 검색한다. 오라클은 http:// 와 slash(/) 또는 문자열의 끝 사이에 이 substring의 최소 3, 최대 4 발생을 검색한다.

SELECT REGEXP_SUBSTR('http://www.oracle.com/products',
                     'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
FROM   DUAL;

REGEXP_SUBSTR
----------------------
http://www.oracle.com/

REGEXP_SUBSTR 함수는 SUBSTR 함수와 마찬가지로 문자열의 일부를 추출합니다. 표 8은 새로운 함수의 사용법을 설명하고 있습니다. 아래 예제에서는 [^,]*, 패턴에 매치되는 문자열이 반환됩니다. 정규 표현식은 공백에 이어 사용된 쉼표를 검색하고, 쉼표가 아닌 문자가 0 회 또는 그 이상 반복되는 패턴을 검색([^,]*) 한 후 마지막으로 또 다른 쉼표를 검색합니다. 이 패턴은 쉼표로 구분된 문자열(comma-separated values)과 유사한 배열을 갖습니다.

SELECT REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,')
FROM   dual

REGEXP_SUBSTR('FIR
------------------
, second field   ,

  --------------------------------------------