[MSSQL] 특정 위치 문자열 추출

Query를 작성하다 보면, 특정 컬럽의 값 중에서 특정 위치의 단어만을 추출해야 하는 경우가 발생하곤 합니다.

그럴 때는 아래처럼, CHARINDEX와 SUBSTRING을 적절히 활용하여 추출 가능합니다.

 

CHARINDEX("찾는 문자", "원문"[, "시작 위치"]) : 원문에서 찾는 문자의 위치 INDEX를 반환

SUBSTRING("원문", "시작 위치", "종료 위치") : 원문의 시작 위치에서 종료 위치까지만 반환

LEN("원문") : 원문의 글자 수 반환

 

SELECT V.TEXT	원문
	 , CHARINDEX('/', V.TEXT)						"첫 번째 슬래시 위치"
	 , SUBSTRING(V.TEXT, 0, CHARINDEX('/', V.TEXT))	"첫 번째 단어"
	 , CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1)																				"두 번째 슬래시 위치"
	 , SUBSTRING(V.TEXT, CHARINDEX('/', V.TEXT) + 1, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) - (CHARINDEX('/', V.TEXT) + 1))	"두 번째 단어"
	 , CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1)																																		"세 번째 슬래시 위치"
	 , SUBSTRING(V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1) - (CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1))	"세 번째 단어"
	 , CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1) + 1)			"마지막 슬래시 위치"
	 , SUBSTRING(V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT, CHARINDEX('/', V.TEXT) + 1) + 1) + 1, LEN(V.TEXT))	"마지막 단어"
  FROM (
        SELECT '바나나/사과/배/딸기' AS TEXT
        UNION ALL
        SELECT '블루베리/파인애플/망고/망고스틴' AS TEXT
       ) V

문자열 추출 결과

CHARINDEX는 찾는 문자열의 위치를 보여줍니다.

 

때문에 SUBSTRING의 종료 위치를 CHARINDEX로 넣어주면 해당 위치의 바로 앞까지 반환해 줍니다.

두 번째 슬래시(/)를 찾는 경우, 첫 번째 슬래시 위치인 CHARINDEX('/', V.TEXT) 다음부터 찾아야 하므로

CHARINDEX('/', V.TEXT) + 1의 위치부터 검색하도록 CHARINDEX 세 번째 항목으로 지정해 주면 됩니다.

 

SUBSTING의 경우 마지막 위치가 시작 위치 기준이므로, CHARINDEX에서 시작 위치 값을 빼주면 추출 가능합니다.

 

Ex. 두 번째 슬래시('/') 위치의 값을 추출하는 경우

# 슬래시('/') 다음 위치부터 추출하므로 +1을 해줌

시작 위치 = CHARINDEX('/', V.TEXT) + 1

# 종료 위치 = 두 번째 슬래시 - 시작 위치

종료 위치 = CHARINDEX('/', V.TEXT) - (시작 위치)

 

주의! 마지막 단어의 경우 8번째 셀에 보이듯이 index값이 0입니다. 때문에 SUBSTRING에 CHARINDEX로 추출이 어렵습니다. 때문에 LEN(V.TEXT)를 종료 위치로 지정해주어야 합니다.