Oracle REPLACE 여러개 - Oracle REPLACE yeoleogae

반응형

오라클에서 REPLACE를 여러개 사용할 경우에는

REGEXP_REPLACE를 사용해주면 되는데

REGEXP(값, '바꿀값||바꿀값', '뭘로바꿀지')

로 이루어져 있는데

여기서 여러 항목을 REPLACE 해주려면

바꿀값에 ||를 붙이면서 연결하면 된다

위 예제는 'AAA,BBB,CCC,DDD' 중

BBB와 DDD를 XXX로 바꾸는 쿼리인데

출력을 해 보니 BBB와 DDD만 바뀌어

AAA,XXX,CCC,XXX로 출력이 된 것이 보인다 

예제에 사용한 쿼리는 다음과 같다

 SELECT REGEXP_REPLACE('AAA,BBB,CCC,DDD', 'BBB|DDD', 'XXX')
   FROM DUAL

반응형

저작자표시

'SQL > Oracle' 카테고리의 다른 글

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

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

오라클 REGEXP_REPLACE 다중(여러개) Replace(치환) 하는 방법

오라클 10g 부터 정규식 함수가 추가 되었다. 

정규식을 사용하여 기존 함수보다 더 많은 기능을 수행한다.

기존 Replace 함수를 사용하여 여러개의 문자를 치환하기 위해서는 

해당 함수를 중첩으로 사용하였다.

REGEXP_REPLACE 함수를 한번 사용하여 여러개의 문자를 치환하는 방법을 알아보자.

 --치환할 문자를 |로 구분하여 입력
 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', 'Stay|Foolish|Steve', '')
   FROM DUAL

 --결과:  Hungry,  . -  Jobs
 ;

 --문자를 제거만 할꺼면 3번재 파라미터는 생략가능
 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', 'Stay|Foolish|Steve')
   FROM DUAL

 --결과:  Hungry,  . -  Jobs
 ;

 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005', 'Stay|Foolish|Steve', '###')
   FROM DUAL

 --결과: ### Hungry, ### ###. - ### Jobs 2005
 ;

 --특수문자 제거
 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', '[[:punct:]]')
   FROM DUAL

 --결과: Stay Hungry Stay Foolish  Steve Jobs
 ;

 --숫자를 제외한 모든문자 제거 (숫자만)
 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs 2005', '[^[:digit:]]')
   FROM DUAL
 
 --결과: 2005
 ;

 --$같은 특수문자를 치환하기 위해서는 Escape 문자(\)를 붙여 줘야함
 SELECT REGEXP_REPLACE('$Stay Hungry, Stay Foolish. - Steve Jobs', '-|\$')
   FROM DUAL

 --결과: Stay Hungry, Stay Foolish.  Steve Jobs

1. 치환할 문자를 |로 구분하여 입력한다

2. 문자를 제거만 할꺼면 3번째 파라미터는 생략 가능하다

3. 3번째 파라미터에 치환될 문자를 입력한다

4. [:punct:] 문자 클래스 사용하여 특수문자만 제거 한다

5. [:digit:] 문자 클래스 사용하여 숫자가 아닌 모든 문자를 제거 한다

6. $같은 특수문자는 앞에 Escape 문자를 붙여줘야 한다