MS SQL_REPLACE, TRANSLATE 함수로 다중 문자열 치환하는 방법

MS SQL에서 문자열을 치환하기 위해서REPLACE 함수나 CASE WHEN 함수를 사용할 수 있습니다
REPLACE 함수는 문자열에 포함된 특정 문자를 지정한 문자로 치환할 수 있는 함수로 결과는 이런 식입니다.

SELECT '2023-08-01' AS 변경전, REPLACE ('2023-08-01', '-', '.') AS 변경후

      
CASE WHEN 함수는 문자열 자체를 통으로 다른 문자로 출력할 수 있는 함수로 결과는 아래와 같습니다.

CREATE TABLE #TEMP (
	NAME NVARCHAR(10)
)
INSERT INTO #TEMP(NAME)
VALUES ('사과'), ('복숭아'), ('포도')

SELECT NAME ,CASE NAME WHEN  '사과' THEN 'APPLE'
                    WHEN '복숭아' THEN 'PEACH'
                    WHEN '포도' THEN 'GRAPH' 
                    ELSE '' END AS 'CASE WHEN 결과'
FROM #TEMP

---

하지만, 한 문자열에서 변경하고 싶은 특정 문자가 여러 개일 경우는 어떻게 할까요?
예를 들어  '찐투두는 블로거야, 짱짱' 이라는 한 문자열에서 찐투두 => JJINTODO  / 블로거 => BLOGER / 짱 => GOOD! 이런 식으로 여러 개의 특정 문자를 변경하여 'JJINTODO는 BLOGER, GOOD!GOOD!' 라는 결과를 얻고 싶을 수 있습니다.

그럴 때 REPLACE를 중첩하여 사용하는 방법이 있습니다.

1. RELPLACE 함수를 사용하여 다중 문자열 치환하기 - 변수 활용

DECLARE @TEST NVARCHAR(50) = '찐투두는 블로거야, 짱짱'
DECLARE @RESULT NVARCHAR(50)

SELECT @RESULT =  REPLACE (@TEST, '찐투두', 'JJINTODO')
SELECT @RESULT =  REPLACE (@RESULT, '블로거', 'BLOGER')
SELECT @RESULT =  REPLACE (@RESULT, '짱', 'GOOD!')
        
SELECT @RESULT

변수를 선언하여 치환 결과를 변수에 저장하는 방식입니다.

2. RELPLACE 함수를 사용하여 다중 문자열 치환하기 - 중첩으로 사용

DECLARE @TEST2 NVARCHAR(50) = '찐투두는 블로거야, 짱짱'
SELECT  REPLACE ( REPLACE ( REPLACE (@TEST2, '찐투두', 'JJINTODO'),'블로거', 'BLOGER'),'짱', 'GOOD!')

여러 개의 특정 문자를 각각 치환하기 위해서 중첩으로 REPLACE 함수를 사용하는 방법도 있습니다.

 

결과는 1번 방식과 2번 방식 모두 동일하게 원하는 결과를 얻을 수 있습니다.

 

---

다만, 치환해야 할 문자가 많다면 쿼리문이 복잡해진다는 단점이 있습니다.

그럴 때는 TRANSLATE 함수를 사용하면 간결하게 쿼리문을 작성할 수 있습니다.

 

3.TRANSLATE 함수 사용법

Server 2017 버전부터 TRANSLATE 함수를 사용하여 문자열을 치환할 수 있습니다.

오라클에서는 오래전 부터 사용이 가능했지만 MSSQL에서는 신규로 추가된 함수입니다.

TRANSLATE ('문자열', '찾을 문자 리스트', '치환 문자 리스트')

SELECT TRANSLATE('MS SQL TRANSLATE', 'SA', 'sa')

찾을 문자와 치환 문자가 문자열로 인식하지 않고, 하나하나의 문자로 인식하며 문자 하나하나 각각 치환됩니다.