[Oracle] 오라클 TRANSLATE 함수 사용법

오라클에서 문자열을 치환하기 위해서는 대부분 replace 함수와 translate 함수를 사용 한다. translate 함수는 사용방법을 정확히 숙지하지 않으면 너무 어렵게 느껴지는 함수이기도 하다.

함수사용법 : translate('문자열', '대상문자', '변환문자')

대상문자와 변환문자는 1:1로 변환이되며, 대상문자에는 있는데 변환문자에는 없으면 해당 문자는 제거 된다.

replace 함수에서는 특정문자를 제거할때 변환문자에 ''을 사용하여 제거하지만, translate 함수에서는 변환문자에 ''을 사용할 수 없다.

예) translate('010-1234-5678', '-', '') : 변환문자에 ''사용할 수 없음

사용예제

SELECT TRANSLATE('hello world!!!', 'hw', 'HW') FROM dual --결과 : Hello World!!! ; SELECT TRANSLATE('hello world!!!', '!', '?') FROM dual --결과 : hello world??? ; SELECT TRANSLATE('hello world!!!', 'hw!', 'HW') FROM dual --결과 : Hello World ; SELECT TRANSLATE('hello world!!!', 'o', ' ') FROM dual --결과 : hell w rld!!!

replace 함수처럼 문자열을 치환하지않고 문자 단위로 치환이 된다. replace 함수로 여러개의 문자를 치환하기 위해서는 함수를 여러번 사용해야 하지만, translate 함수를 사용하면 한번에 치환이 가능하다. 

전화번호 특수문자 제거

WITH TEMP AS ( SELECT '010-1234-5678' TEL_NO FROM DUAL ) SELECT TRANSLATE(TEL_NO, '0123456789'||TEL_NO, '0123456789') FROM TEMP --결과 : 01012345678

대상문자에 원본 전화번호 문자를 결합하여 대상문자의 숫자만 변환문자로 변환되고, 1:1 맵핑이 안되는 문자(추가로 합친)는 제거되는 방법이다

숫자를 특수문자 숫자로 변환

SELECT TRANSLATE('881201', '0123456789', '０１２３４５６７８９') FROM dual --결과 : ８８１２０１

숫자 제거 방법

SELECT TRANSLATE('ab12cd3ef', 'a0123456789', 'a') FROM dual --결과 : abcdef

'a' 라는 대상문자는 'a' 라는 문자로 변환이되고, 대상문자의 숫자(0123456789)는 변환문자에 없기 때문에 제거 된다. 대상문자에 없는 문자(bcdef)는 그대로 출력이 된다.

숫자 체크 방법 (IS_NUMBER)

WITH TEMP AS ( SELECT '1200' PRICE FROM DUAL UNION ALL SELECT '$100' PRICE FROM DUAL ) SELECT PRICE FROM TEMP WHERE TRIM(TRANSLATE(PRICE, '0123456789', ' ')) IS NULL --결과 : 1200

숫자를 공백(' ')으로 치환 후 trim 함수로 공백을 제거 했을때 값이 없으면 해당 문자열은 모두 숫자로만 되어있는 경우이다.

엔터값 제거

WITH TEMP AS ( SELECT 'Steven' || CHR(13)||CHR(10) || 'King' NM FROM DUAL ) SELECT TRANSLATE(NM, ' '||CHR(13)||CHR(10), ' ') RE_NM FROM TEMP --결과 : StevenKing