Oracle REPLACE 대소문자 - Oracle REPLACE daesomunja

REPLACE

• REPLACE(char, search_string, replacement_string)

SELECT REPLACE('oracleStudy','oracle','db') AS result FROM DUAL; -- dbStudy
 
-- 문자열은 대소문자 구분하니 항상 조심
SELECT REPLACE('oracleStudy','Study','learn') AS result FROM DUAL; -- oraclelearn
 
-- 공백 제거
SELECT REPLACE('oracle S t u d y',' ','') AS result FROM DUAL; -- oracleStudy 
 
-- 문자 제거
SELECT REPLACE('oracleStudy','oracle') AS result FROM DUAL; -- Study

REPLACE 함수는 search_string을 탐색하고 정확히 일치하는것을 replacement_string으로 바꿔준다.

그리고 보통 공백을 제거할 때는 LTRIM 이나 RTRIM을 이용하여 앞뒤의 공백을 제거해 주고는 하는데,

문자열의 중간에 있는 공백은 제거하지 못한다.

그럴때 중간에 있는 공백을 제거해주기 위해 REPLACE 함수를 사용하고는 하는데,

이는 문자열 전체에 있는 공백을 모두 제거할 수 있다는 의미이다.

replacement_string이 생략 됐다면 당연히 아무것도 없다는 판단하에 search_string과 일치하는 것들은 삭제된다.

TRANSLATE

• TRANSLATE(expr, from_string, to_string)

SELECT TRANSLATE('oracle DB tocel','olce','!@#$') AS result FROM DUAL; -- !ra#@$ DB t!#$@
 
SELECT TRANSLATE('oracle DB tocel','olce','%^') AS result FROM DUAL; -- %ra^ DB t%^
 
SELECT TRANSLATE('oracle DB tocel','o','&*^') AS result FROM DUAL; -- &racle DB t&cel

TRANSLATE 함수는 REPLACE 함수와 비슷하지만,

문자를 한글자씩 매칭해서 바꾼다는게 큰 차이가 있다.

예제를 통해 설명을 해보자면

1번 예제는 olce를 !@#$로 바꾸라고 했지만 반환값을보면 o는 !, l은 @, c는 #, e는 $ 이런식으로 각각 변한것을 볼 수 있다.

2번 예제는 olce를 %^로 바꾸라고 했으나 o는 %, l은 ^ 로만 바뀌고 나머지만 null처리되어 삭제되는 것을 볼 수 있다.

3번 예제는 o를 &*^로 바꾼다고 선언 했으나 o는 &로만 바뀌는 것을 볼 수 있다.

이처럼 예제를 통해 하나 알아낼 수 있는게, from_string의 각각의 위치와 to_string에 선언된 문자열의 위치가 동일한 녀석들만 짝을 이뤄서 변화하는 것을 알 수 있다.

그래서 보통 REPLACE를 많이 사용하기 때문에 TRANSLATE라는 함수도 있구나 하고 넘어가면 좋을 것 같다.

오라클에서 특정 문자열을 치환하거나 제거하기 위해서는 replace() 함수를 사용하면 된다.

단순 문자열 치환 외에도 엔터값 제거나 <br/>태그를 엔터값 치환에도 많이 사용된다. 

오라클 10g 부터 정규식 사용이 가능한 regexp_replace()함수가 추가 되었다.

함수사용법 : replace(컬럼명, '찾을문자', '변환문자')

--특정문자 치환

replace(A.NM, 'Steven', 'S.')

--특정문자 제거

replace(A.NM, 'King', '')

--엔터값 제거

replace(replace(A.NM, chr(13), ''), chr(10), '')

--탭문자 제거

replace(A.NM, chr(9), '')

--<br/>태그 엔터값으로 치환

replace(A.NM, '<br/>', chr(13)||chr(10))

--전환번호 구분자 제거

replace(A.TELNO, '-', '')

특정문자 치환

WITH TEMP AS ( SELECT 'Steven King' NM FROM DUAL ) SELECT A.NM , REPLACE(A.NM, 'Steven', 'S.') RE_NM FROM TEMP A ----------------------------

- 문자 치환시 대문자, 소문자를 구분하기 때문에 유의해야 한다

특정문자 제거

WITH TEMP AS ( SELECT 'Steven King' NM FROM DUAL ) SELECT A.NM , REPLACE(A.NM, 'King', '') RE_NM FROM TEMP A

---------------------------

엔터값 제거

WITH TEMP AS ( SELECT 'Steven' || CHR(13)||CHR(10) || 'King' NM FROM DUAL ) SELECT A.NM , REPLACE(REPLACE(A.NM, CHR(13), ''), CHR(10), '') RE_NM FROM TEMP A

-------------------------

- 엔터값을 제거하기 위해서는 replace함수를 두번 사용해야 정확히 제거된다

- 윈도우에서 엔터값은 chr(13)chr(10)이며 리눅스에서 엔터값은 chr(10)으로 사용된다

탭문자 제거

WITH TEMP AS ( SELECT 'Steven' || CHR(9) || 'King' NM FROM DUAL ) SELECT A.NM , REPLACE(A.NM, CHR(9), '') RE_NM FROM TEMP A

--------------------------------

- 오라클에서 탭문자는 chr(9)를 사용한다

<br/>태그 엔터값으로 치환

WITH TEMP AS ( SELECT 'Steven' || '<br/>' || 'King' NM FROM DUAL ) SELECT A.NM , REPLACE(A.NM, '<br/>', CHR(13)||CHR(10)) RE_NM FROM TEMP A

-------------------------------

- <br/>태그 치환시 대문자, 소문자를 구분하기 때문에 유의해야 한다

- <br>, <BR/> 등 경우의 수가 많다면 여러번 치환하는게 좋다

전화번호 구분자 제거

WITH TEMP AS ( SELECT '010-4321-6789' TELNO FROM DUAL ) SELECT A.TELNO , REPLACE(A.TELNO, '-', '') RE_TELNO FROM TEMP A

----------------------------------