다른 명령
oracle 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 ;
숫자를 특수문자 숫자로 변환
SELECT TRANSLATE('881201', '0123456789', '0123456789') FROM dual --결과 : 881201
숫자 제거 방법
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