【오라클(Oracle) SQL 함수】 REGEXP_SUBSTR 함수

o REGEXP_SUBSTR

 

---

문 법

 

regexp_substr::=

---

목 적

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

REGEXP_SUBSTR 함수는 문자열에서 정규 표현식 패턴을 검색할 수 있도록 SUBSTR 함수의 기능을 확장한 것이다. REGEXP_INSTR 함수와 유사하지만, 문자열(substring) 의 위치를 반환하는 것이 아니라, 패턴이 일치하는 부분의 문자열(substring) 자체를 반환한다. 이 함수는 일치하는 문자열의 내용은 필요하지만, 소스 문자열 내에서 해당 문자열의 위치는 필요 없는 경우에 유용하다. 이 함수는 문자열을 source_char 과 동일한 문자 집합의 VARCHAR2 또는 CLOB 데이터 형태로 반환한다.

이 함수는 POSIX 정규 표현식 표준과 유니코드 정규 표현식 가이드라인을 준수한다.

 

source_string 인수

source_string 인수는 검색 값으로써 전달되는 문자 표현식이다. 일반적으로 문자열이고, CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 데이터 타입 중에 하나를 사용 할 수 있다.

 

---

pattern 인수

pattern 인수는 정규 표현식이다. 일반적으로 텍스트 리터럴(literal)이고, 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터형 중에 하나를 사용 할 수 있다. 여기에는 최대 512 Byte의 문자를 지정할 수 있다. 만약 pattern 인수의 데이터 타입이 source_char 인수의 데이터 타입과 다르면, 오라클 데이터 베이스는 pattern 인수를 source_char 인수의 데이터 타입으로 변환한다. pattern 인수에서 지정할 수 있는 연산자의 리스트는  Appendix C, " Oracle Regular Expression Support" 참조.

 

---

position 인수

position 인수는 오라클이 검색을 시작하는 source_char 인수의 문자 위치를 나타내는 양의 정수이다. 기본값은 1이며, 오라클이 source_char 인수의 첫 번째 문자에서 검색을 시작하는 것을 의미한다.

 

---

occurrence 인수

occurrence 인수는 source_char 인수에 존재하는 pattern 인수의 패턴 중 몇 번째 패턴을 검색할 것인지를 나타내는 양의 정수이다. occurrence 인수에서 지정한 검색 횟수에 해당하는 문자열의 위치를 반환한다. 기본값은 1이고, 이것은 오라클은 pattern 인수에서 지정한 패턴이 가장 먼저 검색되는 문자열을 검색하는 것을 의미한다. occurrence 인수가 1보다 큰 경우, pattern 인수에서 지정한 패턴과 일치하는 첫 번째 패턴의 문자열을 검색하고, 바로 직후에 위치하는 첫 번째 문자에서 두 번째 패턴의 검색을 시작한다. 이 작업 방식은 첫 번째로 검색된 패턴 문자열의 두 번째 문자에서 두 번째 패턴 문자열의 검색을 시작하는 SUBSTR 함수와는 다르다.

 

---

match_param 인수

match_param 인수는 VARCHAR2 또는 CHAR 데이터 타입의 문자 표현식으로 함수의 기본적인 검색(매칭) 작업 방식을 변경할 수 있다. 이 매개변수의 작업방식은 REGEXP_COUNT 함수와 동일하다.

      match_param 인수에는 다음의 값을 하나 이상을 지정할 수 있다.

리터럴	내용
'i'	대소문자를 구분하지 않고 검색작업을 수행한다.
'c'	대소문자를 구분하여 검색작업을 수행한다.
'n'	임의의 모든 문자와 대응(일치)하는 기호인 마침표(.) 를 사용하여 줄바꿈 문자도 함께 대응하여 처리하도록 할 수 있도록 한다. 이 매개변수를 지정하지 않으면 마침표는 줄바꿈 문자와 대응(일치)하지 않는다.
'm'	소스 문자열을  다중 라인으로 처리한다. 오라클은 캐럿(^) 및 달러 기호($)를 각각 전체 입력 문자열의 시작과 끝으로 해석하는 것이 아니라, 다중 라인(줄바꿈 문자를 고려)을 고려하여서 입력 문자열 내 개별 행의 시작과 끝으로 해석한다. 이 매개변수를 지정하지 않으면 오라클은 줄바꿈 문자가 포함되어서 여러 라인으로 입력된 입력 문자열을 한 줄로 처리한다.
'x'	공백 문자를 무시한다. 기본적으로 공백 문자를 공백 문자로 처리한다.

match_param  인수에 복수의 모순된 값을 지정하면, 오라클은 마지막 문자 값을 이용한다. 예를 들어, 만약 'ic'를 지정하면, 오라클은 대소문자를 구분하여 'c'에 해당하는 검색 작업을 수행한다. 만약 위에서 설명한 문자와 다른 문자를 지정하면, 오라클은 에러를 발생한다.

만약 match_param 인수를 생략하면,

• • 기본 대/소문자 및 악센트 감도는 REGEXP_COUNT 함수의 결정된 데이터 정렬(대조, collation)에 의해 결정된다.
  • period(.)은 새 줄바꿈 문자와 일치하지 않는다.
  • 소스 문자열은 단일행으로써 취급된다.

 

---

subexpr 인수

부분 정규 표현식이 포함된 pattern 인수의 경우, subexpr 인수는 0에서 9까지의 음이 아닌 정수로, 정규 표현식 함수에서 어떤 부분 표현식을 추출할지 결정한다. 이 매개변수는 REGEXP_INSTR 함수와 동일한 의미를 가진다.

다음과 같은 정규 표현식 패턴의 경우

(a(b|c))d

이 패턴에는 두 개의 서브 패턴이 있습니다.

1. (a(b|c)) : 첫 번째 서브 패턴은 a에 이어 b 또는 c 중 하나가 나오는 패턴이다.
2. d : 두 번째 서브 패턴은 d 문자이다.

매개변수 subexpr 인수를 사용하여 다음과 같이 부분 표현식(subexpression)을 추출할 수 있다.

• subexpr = 0: 전체 패턴을 추출.
• subexpr = 1: 첫 번째 서브 패턴을 추출.
• subexpr = 2: 두 번째 서브 패턴을 추출.

 

---

• 참고 도서 : Oracle Regular Expressions

 

---

패키지 함수 비교(Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• REGEXP_SUBSTR Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수내용
INSTR	문자 열중에서 지정한 문자를 지정하여서 처음 나타나는 위치를 숫자로 반환한다. (문자검색)
REPLACE	문자열 char에서 "search-string"을 검색하여서 일치하는 모든 부분을 "replacement_string"으로 변경한다.
REGEXP_REPLACE	문자열에서 지정한 패턴 조건(정규 표현식, 정규식)을 만족하는 부분을 검색하여서 다른 문자열로 치환합니다. 복잡한 치환/검색 조작을 가능하게 합니다.
REGEXP_SUBSTR	문자열에서 지정한 패턴 조건(정규표현식, 정규식)을 만족하는 부분의 문자열을 반환한다.
REGEXP_INSTR	문자열에서 지정한 패턴 조건(정규 표현식)을 만족하는 문자열의 최초의 위치(무슨 문자인지)를 반환한다. 또한, 검색을 시작하는 시작 위치를 지정하는 것도 가능합니다.
SUBSTR	문자열 Char에서 position 문자 위치로부터 substring_length 문자 길이만큼 문자열을 추출하여 반환한다.

 

---

예 제

다음 예제는 문자열을 검하여, 쉼표를 기준으로 구분된 첫번째 하위 문자열(substring)을 검색한다. Oracle 데이터베이스는 쉼표 다음에 하나 이상의 쉼표가 아닌 문자가 존재하고, 해당 문자 다음에 위치한 쉼표를 검색한다. 오라클은 앞뒤 쉼표를 포함한 하위 문자열(substring)을 반환한다.

Oracle Programming

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

 

Results

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

 

---

REGEXP_SUBSTR 함수를 사용하여 URL 문자열에서 특정 패턴을 포함하는 부분 문자열을 추출하는 예제이다. 문자열을 검사하여, http:// 뒤에 하나 이상의 영숫자의 문자열과 옵션으로 마침표(.)로 구성된 부분 문자열을 검색한다. 오라클은 http:// 뒤에 마침표로 구분되는 영숫자로 구성된 문자열이 최소 3개 이상 최대 4 개 이하로 반복되고 slash(/) 또는 문자열의 끝으로 종료되는 형식의 하위 문자열(URL)을 검색한다.

Oracle Programming

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

 

Results

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

 

---

Oracle Programming

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

 

Results

REGEXP_SUBSTR
----------------------
http://www.example.com.aaa.

 

---

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

Oracle Programming

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

 

Results

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

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2023.11 재수정