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

REGEXP_REPLACE 함수

이 함수는 REPLACE함수를 확장한 개념으로 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수입니다. 사용법이 다소 복잡하지만 아주 유용하게 사용되는 함수이므로 익혀야 합니다.

문법
REGXPP_REPALCE(source_char, patten
[, replace_string
[, position
[, occurrence
[, match_param]]]]
)

souce
원본 데이터를 의미합니다. 컬럼명이나 문자열이 올 수 있고 올 수 있는 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 입니다.

patten
찾고자 하는 패턴을 의미합니다. 512바이트까지 정규표현식을 사용하며 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2가 올 수 있습니다.

replace_string
변환하고자 하는 형태입니다. 두번째 패턴에 일치하는 문자 또는 문자열을 찾아서 세번째 모양으로 변환하라는 의미입니다.

position
검색 시작 위치를 지정합니다. 아무런 값을 주지 않을 경우 기본값은 1입니다.

occurrence
패턴과 일치가 발생하는 횟수를 의미합니다. 0은 모든 값을 대체하고 다른 N이라는 숫자를 주면 N번째 발생하는 문자열을 대입합니다.

match_param
기본값으로 검색되는 옵션을 바꿀 수 있습니다. 세부옵션은 아래와 같습니다.
c : 대소문자를 구분해서 검색합니다.
i : 대소문자를 구분하지 않고 검색합니다.
m : 검색 조건을 여러줄로 줄 수 잇습니다.

만약 위 조건 중 c와 i가 중복으로 설정되면 마지막에 설정된 값을 사용하게 됩니다.
ic == c옵션 적용

예제 1. 모든 숫자를 특수 기호로 변경하기
SELECT TEXT, REGEXP_REPLACE(TEXT, '[[:digit:]]', '*') "NO -> CHAR" 
FROM T_REG;

예제 2. 특정 패턴을 찾아서 패턴을 변경하기 (숫자를 찾아 뒤에 -*를 추가)
SELECT TEXT, REGEXP_REPLACE(TEXT, '([0-9])', '\1-*') "ADD CHAR" FROM T_REG; 

( IP의 . 부분을 모두 삭제)
SELECT NO, IP, REGEXP_REPLACE(IP, '\.', '') "DOT REMOVE" 
FROM T_REG2;

(IP의 첫번째 . 만 / 로 변경)
SELECT NO, IP, REGEXP_REPLACE(IP, '\.', '/', 1, 1) "DOT REMOVE" 
FROM T_REG2;  

예제 3. 사용자에게 입력받은 문자 가운데 공백이 여러 개 들어있을 경우 그 공백을 제거시키는 방법

(ID가 'AAA BBB' 처럼 중간에 공백이 있을 경우 'AAA' 와 'BBB' 사이에 공백을 없애고 출력하는 방법)

***'&ID' 대신 'AAA BBB' 로 대체하였습니다.***
SELECT REGEXP_REPLACE('AAA BBB', '( ){1,}', '') FROM DUAL; 

{1,} 부분은 {1}로 사용가능 합니다. 위 예에서 {}내의 숫자는 앞 문자가 나타나는 횟수 또는 범위를 의미합니다.
예를들어 A{5}의 의미는 'A'의 5번 반복인 AAAAA만을 의미합니다.
이 형태의 변형인 A{3,}는 'A'가 세번 이상 반복인 AAA, AAAA, AAAAA... 등을 의미합니다. 
A{3,5}는 AAA, AAAA, AAAAA를 의미하며 AB{2,3}은 뒤에 B가 두번과 세번 반복된 형태인 ABB, ABBB를 의미합니다.

예 1)
SELECT  REGEXP_REPLACE('AAA BBB', '( ){2,}', '') "ONE" ,
REGEXP_REPLACE('AAA  BBB', '( ){2,}', '') "TWO"
FROM DUAL;

ONE, TWO를 살펴보면 공백이 한칸인 첫번째 AAA BBB는 공백이 제거되지 않았고 공백이 투칸인 AAA  BBB는 공벡이 제거 되어 출력 되었습니다.


예 2)
SELECT  REGEXP_REPLACE('AAA BBB', '( ){2,}', '*') "ONE" ,
REGEXP_REPLACE('AAA  BBB', '( ){2,}', '*') "TWO",
REGEXP_REPLACE('AAA   BBB', '( ){2,}', '*') "THR"
FROM DUAL; 

이 예제 2는 공백이 2칸 이상인 문자에서 공백을 * 로 치환하는 문장입니다.
첫번째는 공백이 한칸이라서 그대로 공백이 출력되고 두번째와 세번째는 공백이 각각 두칸과 세칸이여서 *로 바뀐 모습을 볼 수 있습니다.

[0-9]{2} : 숫자 2자리를 의미
ABC[7-9]{2} : ABC77, ABC87, ABC97 등이 해당 됨


예제 4. 사용자가 검색어를 입력할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도 공백이 있어서 모든 공백을 제거하기.

SELECT STUDNO, NAME, ID 
FROM STUDENT
WHERE ID = REGEXP_REPLACE('      75     true', '( ){1,}', '');

검색어 사이의 모든 공백을 제거하고 75true로 만든 후 조회하는 예제입니다.
만약 대소문자 구분 없이 모두 소문자로 바꾸어서 조회하고 싶을 경우는 아래와 같이 하면 됩니다.

SELECT STUDNO, NAME, ID 
FROM STUDENT
WHERE ID = LOWER(REGEXP_REPLACE('      75     TRUE', '( ){1,}', ''));

예 1) 특정 문자열의 형태를 다른 형태로 바꿀 때 '20120324'로 이루어진 데이터를 '2012-03-24' 형태로 변형하기

SELECT REGEXP_REPLACE('20120324', '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})', '\1-\2-\3') FROM DUAL;