[MSSQL] 문자열 치환 방법 3가지 (REPLACE 함수)

SQL Server에서 문자열을 치환하기 위해서는 REPLACE 함수를 자주 사용한다. 그러나 문자열의 특정 영역을 치환 또는 마스킹 킹 처리를 할 때는 STUFF 함수를 사용하면 조금 더 편리하게  할 수 있다. 또한 여러 개의 문자를 다중치환할 때는 TRANSLATE 함수를 사용하면 쿼리문을 조금 더 짧고 명료하게 작성할 수 있다.

 

MSSQL 문자열 치환 방법

목차 문자열 치환 방법 3가지 REPLACE 함수를 사용하여 문자열 치환 방법 STUFF 함수를 사용하여 문자열 치환 방법 TRANSLATE 함수를 사용하여 문자열 치환 방법

 

문자열 치환 방법 3가지

SELECT REPlACE('MSSQL', 'S', 'X')
     , STUFF('MSSQL', 2, 2, 'XX')
     , TRANSLATE('MSSQL', 'S', 'X')

 

 

위의 예제 결과를 보면 3가지 함수 모두 동일한 결과가 출력되었지만, 사용하는 방법과 사용해야 할 상황이 모두 다르므로 아래의 함수 사용법을 참고하면 쉽게 이해할 수 있을 것이다.

 

REPLACE 함수를 사용하여 문자열 치환 방법

SELECT REPLACE('Microsoft SQL Server', 'Microsoft', 'MS')

 

 

REPLACE('문자열', '찾을 문자열', '치환 문자열')

위의 예제를 보면 'Microsoft' 문자열이 'MS' 문자열로 치환된 것을 확인할 수 있다.

 

REPLACE 함수는 대소문자 구분 없이 치환 

SELECT REPLACE('Microsoft SQL Server', 'MICROSOFT', 'MS')
     , REPLACE('Microsoft SQL Server', 'microsoft', 'MS')

 

 

SQL Server에서는 기본 설정이 대문자, 소문자 구분 없이 찾아서 치환된다.

 

REPLACE 함수 사용 시 대소문자를 구분하여 치환하는 방법

SELECT REPLACE('Microsoft SQL Server', 'microsoft', 'MS')
     , REPLACE('Microsoft SQL Server' COLLATE Korean_Wansung_CS_AS, 'microsoft', 'MS')

 

 

COLLATE Korean_Wansung_CS_AS 옵션을 사용하면 대문자, 소문자가 정확히 일치해야 치환된다.

_CS_AS: 대소문자 구분

_CI_AS: 대소문자 구분 없이

 

특수문자 제거 또는 치환 방법

SELECT REPLACE('2022-12-15', '-', '')
     , REPLACE('2022-12-15', '-', '/')

 

 

REPLACE 함수를 사용하면 특수문자를 제거하거나 특정 특수문자로 치환할 때 유용하다.

 

STUFF 함수를 사용하여 문자열 치환 방법

SELECT STUFF('Microsoft SQL Server', 1,  9, 'MS')
     , STUFF('Microsoft SQL Server', 11, 3, 'XXX')
     , STUFF('Microsoft SQL Server', 15, 6, '')

 

 

STUFF('문자열', '치환 문자열 시작 위치', '치환 문자열 길이', '치환 문자열')

STUFF 함수를 사용하면 특정 위치의 문자열을 쉽게 치환할 수 있다. 

 

STUFF 함수를 사용하여 문자열 마스킹 방법

SELECT STUFF('홍길동', 2, 1, 'O')
     , STUFF('010-1234-5678', LEN('010-1234-5678')-2, 3, 'XXX')

 

 

이름 또는 전화번호를 식별하지 못하도록 마스킹 처리할 때 유용하다.

 

TRANSLATE 함수를 사용하여 문자열 치환 방법

SELECT TRANSLATE('Microsoft SQL Server', 'SQL', 'ⓢⓠⓛ')

 

 

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

TRANSLATE 함수를 처음 접하면 REPLACE 함수와 유사하게 보이는데 결과는 다르다.

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

 

아래의 예제를 보면 조금 더 쉽게 이해할 수 있을 것이다.

 

여러 개의 문자를 다중치환 방법

SELECT TRANSLATE('2022.12.15, 2022/12/16', './', '--')
     , REPLACE(REPLACE('2022.12.15, 2022/12/16', '.', '-'), '/', '-')

 

 

여러 개의 문자를 각각 치환하기 위해서 REPLACE 함수를 사용할 경우 중첩으로 사용해야 하며, 치환해야 할 문자가 많다면 쿼리문이 많이 복잡해질 것이다. TRANSLATE 함수를 사용하면 간결하게 쿼리문을 작성할 수 있다.

 

TRANSLATE 함수에 대해서 조금 더 자세히 알고 싶으면 아래를 참고하면 된다.

[MSSQL] TRANSLATE 함수 사용법