[오라클 레퍼런스 함수] REGEXP_SUBSTR - 정규 표현식 패턴을 사용하여 부분 문자열 추출

★

 

 

 

REGEXP_SUBSTR

 

구문

REGEXP_SUBSTR ( source_char, pattern
                [, position
                   [, occurrence
                      [, match_param
                         [, subexpr ]
                      ]
                   ]
                ]
              )

 

목적

REGEXP_SUBSTR은 SUBSTR 함수의 기능을 확장하여 문자열에서 정규 표현식 패턴을 검색할 수 있도록 합니다. 또한 REGEXP_INSTR과 유사하지만, 부분 문자열의 위치 대신 해당 부분 문자열 자체를 반환합니다. 이 함수는 일치하는 문자열의 내용이 필요하지만 원본 문자열에서의 위치는 필요하지 않은 경우 유용합니다. 이 함수는 문자열을 VARCHAR2 또는 CLOB 데이터로 반환하며, 반환되는 문자열은 원본 문자열과 동일한 문자 집합을 가집니다.

이 함수는 POSIX 정규 표현식 표준과 유니코드 정규 표현식 가이드라인을 준수합니다. 자세한 내용은 Oracle Regular Expression Support을 참조하십시오.

 

• source_char는 검색 값으로 사용되는 문자 표현식입니다. 일반적으로 문자 열이며 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB 또는 NCLOB 데이터 유형일 수 있습니다.
• pattern은 정규 표현식입니다. 일반적으로 텍스트 리터럴이며 CHAR, VARCHAR2, NCHAR 또는 NVARCHAR2 데이터 유형일 수 있습니다. 최대 512바이트까지 포함할 수 있습니다. pattern의 데이터 유형이 source_char의 데이터 유형과 다른 경우 Oracle 데이터베이스는 pattern을 source_char의 데이터 유형으로 변환합니다. pattern에서 사용할 수 있는 연산자 목록에 대해서는 Oracle Regular Expression Support을 참조하십시오.
• position은 Oracle이 검색을 시작할 source_char의 문자 위치를 나타내는 양의 정수입니다. 기본값은 1이며, 이는 Oracle이 source_char의 첫 번째 문자에서 검색을 시작함을 의미합니다.
• occurrence은 source_char에서 pattern의 발생을 나타내는 양의 정수입니다. 기본값은 1이며, 이는 Oracle이 pattern의 첫 번째 발생을 검색함을 의미합니다. 만약 occurrence이 1보다 큰 경우 데이터베이스는 pattern의 첫 번째 발생 다음 첫 번째 문자부터 두 번째 발생을 검색하며 이어갑니다. 이 동작은 SUBSTR 함수와 다릅니다. SUBSTR 함수는 첫 번째 발생의 두 번째 문자에서 검색을 시작합니다.
• match_param은 VARCHAR2 또는 CHAR 데이터 유형의 문자 표현식으로, 함수의 기본 매치 동작을 변경할 수 있게 합니다. 이 매개변수의 동작은 REGEXP_COUNT 함수와 동일합니다. 자세한 내용은 REGEXP_COUNT를 참조하십시오.
• 서브표현식이 포함된 pattern의 경우 subexpr은 0에서 9 사이의 음수가 아닌 정수로, pattern의 어떤 서브표현식을 함수가 반환해야 하는지 나타냅니다. 이 매개변수는 REGEXP_INSTR 함수와 동일한 의미를 가집니다. 자세한 내용은 REGEXP_INSTR을 참조하십시오.

 

참고:

• REPLACE 및 REGEXP_INSTR
• REGEXP_REPLACE 및 REGEXP_LIKE 조건
• Oracle Database Globalization Support Guide의 부록 C는 REGEXP_SUBSTR 함수가 source_char의 문자와 pattern의 문자를 비교할 때 사용하는 정렬 결정 규칙(collation determination rules) 및 문자열 반환 값에 할당되는 정렬을 정의하는 정렬 파생 규칙(collation derivation rules)에 대한 정보를 제공합니다.

 

예제

다음 예제는 문자열을 검사하여 쉼표로 둘러싸인 첫 번째 부분 문자열을 찾습니다. Oracle Database는 쉼표 다음에 하나 이상의 비쉼표 문자가 나오는 쉼표를 검색합니다. Oracle은 해당 부분 문자열을 반환하며 앞뒤의 쉼표를 포함합니다.

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

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

 

다음 예제는 문자열을 검사하여 http:// 뒤에 하나 이상의 영숫자 문자 및 선택적으로 마침표(.)가 오는 부분 문자열을 찾습니다. Oracle은 이 부분 문자열이 http://와 슬래시(/) 또는 문자열의 끝 사이에 최소 세 번부터 최대 네 번까지 나타나는 경우를 검색합니다.

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

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

 

다음 두 가지 예제에서는 subexpr 인자를 사용하여 pattern의 특정 하위 표현식을 반환합니다. 첫 번째 문장은 pattern의 첫 번째 하위 표현식을 반환합니다.

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) 
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
123

 

다음 문장은 pattern의 네 번째 하위 표현식을 반환합니다.

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) 
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
78

 

REGEXP_SUBSTR pattern matching: 예제

다음 문장은 테이블 regexp_temp를 생성하고 그 안에 값을 삽입합니다:

CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20));

INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe');

 

다음 예제에서는 email 열을 조회하고 유효한 이메일 주소를 검색하는 문장입니다:

SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email" FROM regexp_temp;

EMPNAME  Valid Email
-------- -------------------
John Doe johndoe@example.com
Jane Doe

 

다음 예제에서는 email 열을 조회하고 유효한 이메일 주소의 개수를 반환하는 문장입니다:

SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email", REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') "FIELD_WITH_VALID_EMAIL" FROM regexp_temp;

EMPNAME		Valid Email			FIELD_WITH_VALID_EMAIL
--------	-------------------	----------------------
John Doe	johndoe@example.com	1
Jane Doe

 

다음 예제에서는 문자열에서 숫자와 알파벳이 추출됩니다:

with strings as (   
  select 'ABC123' str from dual union all   
  select 'A1B2C3' str from dual union all   
  select '123ABC' str from dual union all   
  select '1A2B3C' str from dual   
)   
  select regexp_substr(str, '[0-9]') First_Occurrence_of_Number,    
         regexp_substr(str, '[0-9].*') Num_Followed_by_String,   
         regexp_substr(str, '[A-Z][0-9]') Letter_Followed_by_String   
  from strings;

FIRST_OCCURRENCE_OF_NUMB NUM_FOLLOWED_BY_STRING   LETTER_FOLLOWED_BY_STRIN
------------------------ ------------------------ ------------------------
1			 123			  C1
1			 1B2C3			  A1
1			 123ABC
1			 1A2B3C 		  A2

 

다음 예제에서는 승객 이름과 항공편 정보가 문자열에서 추출됩니다:

with strings as (    
  select 'LHRJFK/010315/JOHNDOE' str from dual union all    
  select 'CDGLAX/050515/JANEDOE' str from dual union all    
  select 'LAXCDG/220515/JOHNDOE' str from dual union all    
  select 'SFOJFK/010615/JANEDOE' str from dual    
)    
  SELECT regexp_substr(str, '[A-Z]{6}') String_of_6_characters,   
         regexp_substr(str, '[0-9]+') First_Matching_Numbers,   
         regexp_substr(str, '[A-Z].*$') Letter_by_other_characters,     
         regexp_substr(str, '/[A-Z].*$') Slash_letter_and_characters     
  FROM strings;

STRING_OF_6_CHARACTERS	FIRST_MATCHING_NUMBERS	LETTER_BY_OTHER_CHARACTERS	SLASH_LETTER_AND_CHARACTERS
----------------------	----------------------	--------------------------	---------------------------
LHRJFK	                010315	                LHRJFK/010315/JOHNDOE	      	/JOHNDOE
CDGLAX	                050515	                CDGLAX/050515/JANEDOE	      	/JANEDOE
LAXCDG	                220515	                LAXCDG/220515/JOHNDOE	      	/JOHNDOE
SFOJFK	                010615	                SFOJFK/010615/JANEDOE	      	/JANEDOE

 

---

출처: 오라클 레퍼런스

원문 링크: Oracle REGEXP_SUBSTR 함수 문서