오라클 replace 여러개(문자) 치환하기( regexp_replace 사용 )

출처: 오라클(https://docs.oracle.com/)

1. 한 행에 여러개 문자 치환

# replace 반복(비효율적)
SELECT REPLACE(replace('java apple python banana', 'apple', 'fruit'), 'banana', 'fruit') FROM dual;

# regexp_replace 사용
SELECT regexp_replace('java apple python banana', 'apple|banana', 'fruit') FROM dual;

 

2. 컬럼에 여러개 문자 치환

다음과 같은 값이 있다고 가정하자. 언더바 뒤의 숫자만 활용하기 위해 숫자 앞의 값은 삭제하려 한다.

WITH TEMP AS (
	SELECT 'A_14' AS C FROM DUAL
	UNION ALL
	SELECT 'B_4' AS C FROM DUAL
	UNION ALL
	SELECT 'C_3' AS C FROM DUAL
	UNION ALL
	SELECT 'D_100' AS C FROM DUAL
)
SELECT * FROM TEMP;

A_14
B_4
C_3
D_100

1. REPLACE 함수를 중복하여 사용

SELECT REPLACE(REPLACE(REPLACE(REPLACE(C, 'A_', ''), 'B_', ''), 'C_', ''), 'D_', '') FROM TEMP;

14
4
3
100

원하는 값으로 나왔으나 가독성이 떨어질뿐만 아니라 성능에 심각한 영향을 미친다.(문자열관련 함수는 코스트가 높다)

 

2. REGEXP_REPLACE 함수 사용

SELECT REGEXP_REPLACE(C, 'A_|B_|C_|D_', '') FROM TEMP;

14
4
3
100

간단하게 위 문제를 해결할 수 있다. 오라클에서 제공하는 REGEXP_ 함수는 정규식표현을 입력받는 함수로서 본 문제와 같이 특정 포맷을 갖는 경우 정규식을 적용할 수 있다.

정규식 적용

SELECT REGEXP_REPLACE(C, '^(\w+_)(\d+)$', '\1\2') FROM TEMP;

A_14
B_4
C_3
D_100

SELECT REGEXP_REPLACE(C, '^(\w+_)(\d+)$', '\2') FROM TEMP;

14
4
3
100

매칭그룹을 역슬러쉬 매칭번호로 접근할 수 있다.