[MSSQL] TRANSLATE 함수 사용법 (여러개 치환, 다중 치환)

SQL Server 2017 버전부터 TRANSLATE 함수를 사용할 수 있게 되었다. 오라클에서는 오래전 부터 사용이 가능했지만 MSSQL에서는 신규로 추가된 함수이다.

 

REPLACE 함수를 사용하여 여러 개의 문자를 치환하기 위해서는 REPLACE 함수를 중첩해서 사용해야 하는데, 이런 경우 쿼리문이 복잡해진다.

 

TRANSLATE 함수를 사용하여 여러 개의 문자를 치환하는 것은 간단하지만, 치환하지 않고 제거는 쉽지 않기 때문에 이런 경우는 REPLACE 함수를 사용하는 것이 효율적일 수 있다.

 

MSSQL TRANSLATE 함수

TRANSLATE("문자열", "바꿀문자", "변환문자")

 

바꿀문자와 변환문자의 길이는 동일해야한다. 

문자열에 바꿀문자가 있을 경우 바꿀문자의 자리수에 해당하는 변환문자로 치환된다.

 

TRANSLATE 함수는 문자 단위로 치환되며, 바꿀문자와 변환문자가 1:1로 매핑된다.

※ 오라클 데이터베이스 TRANSLATE 함수는 바꿀문자와 변환문자의 길이가 일치하지 않아도 된다. 

 

아래의 예제를 보면서 다시 한번 이해를 해 보자.

 

SELECT TRANSLATE('SQL Server 2017', 'S', 's')                   AS result1
     , TRANSLATE('SQL Server 2017', 'SQL', 'sql')               AS result2
     , TRANSLATE('SQL Server 2017', '1234567890', '          ') AS result3

 

 

result1 : 문자열에 "S"가 존재할 경우 "s"로 모두 치환한다.

result2 : 문자열에 "S", "Q", "L"이 존재할 경우 "s", "q", "l"로 치환한다.

result3 : 문자열에 숫자가 존재할 경우 공백(" ")으로 치환한다.

 

SELECT TRANSLATE('2021.02.03, 2021/02/03, 2021_02_03', './_', '---') AS result

 

 

result : 날짜 문자의 특수기호(./_)를 동일한 기호(-)로 모두 치환한다.

 

SELECT TRANSLATE('202102', '0123456789', '０１２３４５６７８９') AS result

 

 

result : 문자열의 숫자를 동일한 의미의 특수기호 숫자로 치환한다.

 

WITH mon AS (
    SELECT '₩1100' AS price UNION ALL
    SELECT '¥100'  AS price UNION ALL
    SELECT '€0.8'  AS price UNION ALL
    SELECT '$1'    AS price 
)

SELECT TRIM(TRANSLATE(price, '₩¥€$', '    ')) AS result
  FROM mon

 

 

result : 문자열의 화폐 기호를 공백(" ")으로 치환 후 TRIM 함수를 이용하여 공백을 제거 한다.

 

TRANSLATE 함수를 이용하여 문자를 제거하기 위해서는 공백(" ")으로 치환 후 TRIM 함수를 사용할 수 있으나, 문자열의 앞 뒤가 아닌 중앙에 있는 문자는 제거하기는 쉽지 않다.

 

중앙의 문자를 제거하기 위해서는 특수한 기호로 치환 후 REPLACE 함수로 제거하는 방법이 가능하다. 오라클의 TRANSLATE 함수는 바꿀문자와 변환문자의 길이가 일치하지 않아도 사용 가능하여 이점을 이용하여 쉽게 문자를 제거할 수 있는데 MSSQL의 TRANSLATE 함수가 조금 아쉬운 면이 있다.