오라클 regexp_replace - olakeul regexp_replace

Oracle Regular Expression 함수 regexp_replace( ) 는 replace( ) 함수에 정규표현식을 쓸 수 있도록 확장한 함수 입니다.

replace( ) 함수가 대상문자열에서 특정 문자열(Search String) 찾아서 다른 문자열로 바꿔치기 해주는 함수인데,

regexp_replace( ) 함수는 Search String 대신에 "정규표현식"을 쓴다는 점이 다릅니다.

regexp_replace() 함수의 구문(Syntax)은 다음과 같습니다.

source_char : 대상 문자열

pattern : 정규표현식 패턴

replace_string : 바꿔치기할 문자열

position : 문자열내에서 (패턴을 체크할) 처음 시작 위치

occurrence : 몇번째 일치하는 건지.  ( 0 이면 전부 바꿔치기 )

match_param : 'i' (대소문자 무시),  'c' (대소문자 구분)

예제를 통해서 사용법을 확인해보면,

♣ 문자열내에서 'CD' 를 '--' 으로 바꿔치기 (대소문자 구분)

'c' 인자를 줬기 때문에 대소문자를 구분해서 cd 는 놔두고, CD 문자들만 -- 로 대체되었습니다.

5번째 인자에 0 을 줬기 때문에 일치하는 모든 문자열을 바꿔치기 합니다.

0 이 아닌 값을 주면, 일치하는 n 번째 문자열만 바꿔치기 할 수 있습니다.

♣ 문자열에서 숫자만 모조리 제거하기

패턴 [^[:alpha:]] 에서 [:alpha] 는 앞파벳(대소문자포함)을 가리키는 문자셋입니다.

앞에 ^가 붙어있으니 부정의 의미가 됩니다. 즉, 알파벳이 아닌것들을 찾아라~~ 입니다.

regexp_replace( ) 함수에서 3번째 replace_string 들어갈 자리를 지정하지 않으면 null 을 의미합니다.

즉, 알파벳이 아닌 놈들을 찾아서 모두 null 로 바꿔라~  모두 제거하라~  가 되겄습니다.

♣ 문자열에서 알파벳문자들만 특수문자 # 으로 대체하기

알파벳이 아니라 숫자를 바꾸려면 [[:alpha:]] 대신에 [[:digit:]] 를 사용하면 됩니다.

물론, [[:digit:]] 대신에 [[0-9]] 를 써도 되고, [[:alpha:]] 대신에 [A-Za-z] 를 써도 됩니다.

♣ 문자열에서 한글 문자들만 제거하기

UTF8 한글의 경우, '가' 글자부터 시작해서 '힟' 으로 끝나기 때문에 [가-힟] 으로 패턴을 지정하면 모든 한글이 됩니다.

EUC-KR 한글의 경우에는 마지막 글자가 '힟' 이 아니라 '힝' 입니다.

그래서 EUC-KR 인 경우는 패턴문자열을 [가-힝] 으로 지정해줘야 모든 한글이 잡히게 됩니다.

▶ 참고 : 오라클 정규표현식(Regular Expression) 소개 메인 페이지

이 함수는 replace 함수를 확장한 개념으로 주어진 문자역에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수이다.

문법

REGEXP_REPLACE (source_char, pattern

                         [, replace_string

                         [, position

                         [, occurrence

                         [, match_param[[[

)

첫번째 인수 Source_char 

 : 원본데이터, 컬럼명이나, 문자열이 올수 있다.

두번쨰 인수 pattern

 : 찾고자 하는 패턴을 의미

세번째 인수 replace_string

 : 변환하고자 하는 형태

네번째 인수 position 

 : 검색 시작 위치를 지정, 아무런 값도 주지 않을 경우 기본값 : 1

다섯 번째 인수 occurrence

 : 패턴과 일치가 발생하는 횟수를 의미, 0은 모든 값을 대채, 다른 n이란 숫자를 주면 n번째 발생하는 문자열을 대입

여섯 번째 인수 match_parameter

 : 기본값으로 검색되는 옵션을 바꿀수 있다.

   - c : 대소문자를 구분해서 검색

   -  i : 대소푼자를 구분하지 않고 검색

   - m : 검색 조건을 여러 줄로 줄 수 있음

   - c와 i가 중복으로 설정되면 마지막에 설정된 값을 사용 ex) ic가 중복으로 절정되면  c 옵션 적용

REGEXP_REPLACE 함수 실습

모든 숫자를 특수 기호로 변경하기

SELECT text, regexp_replace(text, '[[:digit:]]', '+') "숫자->기호" FROM TEST

특정 패턴을 찾아서 패턴을 추가하기

SELECT text, regexp_replace(text, '([0-9])', '\1*') "숫자->패턴" FROM TEST

지역번호가 2자리이고 전화국번이 4자리인 전화번호인 학생 찾기

SELECT NAME, tel FROM student 

WHERE regexp_replace(tel, '(\d{2})\)(\d{4})\-(\d{4})','\2') > '5000'

전공이 101번인 학생의 이름 변경하기 (홍-길-동)

SELECT name, RTRIM(regexp_replace(name, '(.)', '\1-'), '-') "변경후" FROM student

WHERE deptno1=101

특정 문자열을 다른 형태로 바꿀때

SELECT REGEXP_REPLACE('20120324', 

'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',

'\1 : \2 : \3') FROM dual