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

o REGEXP_REPLACE

 

---

문 법

 

regexp_replace::=

---

목 적

문자열에서 지정한 패턴 조건(정규 표현식, 정규식)을 만족하는 부분을 검색하여서 다른 문자열로 치환합니다. 복잡한 치환/검색 조작을 가능하게 합니다.

REGEXP_REPLACE 함수는 문자열에서 정규 표현 패턴을 검색할 수 있도록 REPLACE 함수의 기능을 확장한 것이다. 기본적으로, 이 함수는 source_string 인수에서 정규 표현식 패턴이 검색 될 때마다 검색된 패턴 문자열을 replace_string 인수의 문자열로 대체한 문자열을 반환한다. 반환되는 문자열은 source_char 인수와 동일한 문자 집합의 문자열을 사용한다. 이 함수는 만약 첫 번째 인수가 LOB가 아니라면 VARCHAR2를 반환하고, 첫 번째 인수가 LOB인 경우에는 CLOB를 반환한다.

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

 

source_string 인수

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

 

---

pattern 인수

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

 

---

replace_string 인수

replace_string 인수는 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 데이터 타입 중에서 사용 할 수 있다. 만약 replace_string 인수가 CLOB 또는 NCLOB라면, 오라클은 replace_string 인수의 문자열을 32K로 잘라버린다. replace_string 인수의 문자열에는 \n 형식의 하위 표현식으로 최대 500개의 역참조(후방참조, backreferences)를 지정 할 수 있다. 여기서 n은 1에서 9 사이의 숫자이다. replace_string 인수에서 백슬래시(\) 문자를 포함하려면 백슬래시 문자 앞에 백슬래시 이스케이프 문자(\\)를 붙여야 한다. 예를 들어, \2 문자열을 다른 문자열로 대체하려면 \\2를 입력해야 한다. 역참조 표현식에 대한 자세한 내용은 '오라클 정규식 지원', 표 D-1의 참고 사항을 참조하라.

Oracle Program

select regexp_replace('가나다라마바사아자', '(.)(.)(.)(.)(.)(.)(.)(.)(.)', '\9 \8 \7 \6 \5 \4 \3 \2 \1') TEXT_BACK
from   dual

 

Results

       TEXT_BACK
-------------------------
자 아 사 바 마 라 다 나 가

 

---

position 인수

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

 

---

occurrence 인수

occurrence 인수는 변경(치환, 대체) 작업이 발생하는 횟수을 나타내는 음수가 아닌 정수이다. 

• 만약 0을 지정하면, 오라클은 일치하는 모든 항목을 변경한다.
• 만약 양의 정수 n을 지정하면, 오라클은 n번째 항목에 대하여 변경 작업을 수행한다.

occurrence 인수가 1보다 큰 경우, 데이터베이스는 pattern 인수에서 지정하는 패턴과 일치하는 첫 번째 패턴의 문자열을 검색하고, 바로 직후에 위치하는 첫 번째 문자에서 두 번째 패턴의 검색을 시작한다. 이 작업 방식은 첫 번째로 검색된 패턴 문자열의 두 번째 문자에서 두 번째 패턴 문자열의 검색을 시작하는 INSTR 함수와는 다르다.

 

---

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(.)은 새 줄바꿈 문자와 일치하지 않는다.
  • 소스 문자열은 단일행으로써 취급된다.

 

---

• 참고 도서 : Oracle Regular Expressions 참조

 

---

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

• REGEXP_REPLACE Oracle Function 프로그래밍 비교

 

---

관련 함수

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

 

---

예 제

다음 예제는 phone_number를 조사하여, xxx.xxx.xxxx 패턴을 검색한다. 오라클은 이 패턴을 (xxx) xxx-xxxx 형식으로 출력 서식을 변경한다.

Oracle Program

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM  employees;

 

Results

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

 

---

다음 예제는 country_name을 조사한다. 오라클은 문자열 내에서 NULL이 아닌 각 문자 뒤에 공백을 추가 삽입한다.

Oracle Program

SELECT REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
FROM   countries;

 

Results

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .

 

---

다음 예제는 문자열을 조사하여 2개 이상의 공백을 찾는다. 오라클은 2개 이상 연속적으로 붙어있는 공백을 단일 공백으로 대체한다.

Oracle Program

SELECT REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                      '( ){2,}', ' ') "REGEXP_REPLACE"
FROM   DUAL;

 

Results

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

 

---

특정 문자열을 다른 문자열로 대체하는 기존의 REPLACE SQL 함수의 기능을 잠시 되짚어 보겠다. 데이터의 텍스트에 필요 이상의 공백 기호가 존재하는 상황에서, 이를 단일 공백 기호로 대체하는 경우를 가정해보자. REPLACE 함수를 사용할 때에는 대체할 공백 기호의 숫자를 정확하게 지정해야 한다. 하지만, 필요 없는 공백의 수가 일정하리라는 보장은 없다. 아래 예는 Joe와 Smith사이에 3 개의 공백 기호가 존재하는 경우를 검색하고 있다. REPLACE 함수의 매개변수는 두 개의 공백 기호를 하나의 공백 기호로 대체할 것을 명시하고 있다. 하지만 Joe와 Smith 사이에 3 개의 공백 기호가 존재하는 경우에는 여전히 필요 없는 공백이 하나 남게 된다.

Oracle Program

SELECT REPLACE('Joe   Smith','  ', ' ') AS replace
FROM   dual

 

Results

REPLACE
---------
Joe Smith

 

---

REGEXP_REPLACE 함수는 한층 개선된 문자열 대체 기능을 제공한다. 아래 쿼리는 두 개 또는 그 이상의 공백 기호를 하나의 공백 기호로 대체한다. ( ) 서브 표현식은 하나의 공백 기호를 포함하며  {2,}의 조건에 의해 지정된 대로 2 회 또는 그 이상 반복되는 조건을 명시한다.

Oracle Program

SELECT REGEXP_REPLACE('Joe   Smith','( ){2,}', ' ') AS RX_REPLACE
FROM   dual

 

Results

RX_REPLACE
----------
Joe Smith

 

---

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

 

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

2023.12 수정