DB관련/SQL Server

[MSSQL] 문자열 바꾸기 (치환) - REPLACE, STUFF, TRANSLATE (group by 에서 사용한 예제 포함)

saltdoll 2020. 2. 28. 04:12
반응형

MSSQL의 문자열 치환 함수는 REPLACE가 매우 유용합니다.

치환 속도와 결과가 괜찮습니다.

특히, 조건문이나 그룹 정렬 문에서 사용하면 참 유용하게 사용이 가능합니다.

아래의 "실전 예제 >>"에서 사용 예를 남겨봅니다.

 

Replace() 함수로 특정 문자열 바꾸기

REPLACE

문자열 치환 - 특정 문자열 값을 바꿔주는 함수 (REPLACE, STUFF)

--문법--

REPLACE('문자열','치환예정문자','치환할문자')

 

--예시--

REPLACE('ABCDEFG','DEF','XXX')

--MY_TABLE에서 이름(NM_KOR)을 '이'씨를 '김'씨로 바꿔서 출력--

SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

 

 

 

 

실전 예제 >>

"Event 10% OFF $1.49-$0.15"에서   "10% OFF"  이벤트 이름 갯수 구하기

  SELECT ITEM_NAME AS EVENT_NAME
  FROM ORDER_DTL
  WHERE ITEM_TYPE=5 AND ORDER_STATUS='P' AND ITEM_NAME LIKE ('Event%')
결과

EVENT_NAME
Event Fam and Friend $2.59-$1.30
Event Fam and Friend $7.99-$3.99
Event 10% OFF $11.49-$1.15
Event 10% OFF $8.49-$0.85
Event 10% OFF $7.99-$0.80

...

다음과 같은 상황에서 있을 때,

GROUP BY에 REPLACE와 LEFT를 사용해서 처리하면 유용합니다.

SELECT REPLACE( LEFT(item_name,CHARINDEX('$', item_name)-1),'Event','') as EVENT_NAME
      ,count(*) as CNT
 FROM ORDER_DTL
 WHERE ITEM_TYPE=5 AND ORDER_STATUS='P' and ITEM_NAME LIKE ('Event%')
 GROUP BY REPLACE( LEFT(item_name,CHARINDEX('$', item_name)-1),'Event','')
결과
EVENT_NAME          CNT  
10% OFF                    112  
Fam and Friend          152

 

 

SSN의 '-'없애는 예제>>

SSN의 '-'의 replace처리
- 특수 문자 없에기

 

STUFF

지정된 문자열의 시작 위치와 크기를 지정하여 원하는 문자로 치환(STUFF)

--문법--

STUFF('문자열', '시작 위치', '크기', '치환할 문자')

 

--예시--

STUFF('ABCDEFG',2,3,'XXX')

--MY_TABLE에서 이름(NM_KOR) 칼럼의(NM_KOR) 이순신을 이성계로 바꿔서 출력--
SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

 

 

 

TRANSLATE

TRANSLATE 함수(function)는 SQL Server 2017 이후에 추가되었습니다.

문자열 변환을 특정 Single Character 단위로 찾아서 바꿔 줄수 있습니다. 

Syntax

TRANSLATE (@InputString, @FromString, @ToString)

 

Arguments

@InputString - String where the function is applied on, to translate a specified set of characters to another.
@FromString - Character(s) to be searched within @InputString.
@ToString - Replacement character(s).

 

DECLARE @InputString as VARCHAR(50) = '[#91]7777,7777,77';

SELECT @InputString as BeforeTranslate, 
       TRANSLATE(@InputString,'[#],','(+)-') as AfterTranslate;

특수 문자형태 포멧을 변경하기
특정 문자를 숫자로 변경하기

 

참조:

(Blog) 코딩 팩토리: [MSSQL] 문자열 치환 (REPLACE,STUFF) 사용법 & 예제  

(Site) Tech-Recipes: How To Use TRANSLATE Function In SQL Server

반응형