[MSSQL] 문자열 나누기 - SPLIT

/**
 * SPLIT 함수 생성
 * ex. SELECT TXT, SORT FROM DBO.FN_STR_SPLIT('TEST1;TEST2;TEST3', ';')
 */
CREATE FUNCTION [dbo].[FN_STR_SPLIT]
(
    @INSTR    VARCHAR(MAX)    = NULL        -- 입력내용
  , @KEY    CHAR(1)            = ';'        -- 구분자
)
RETURNS @ARRAY TABLE (TXT VARCHAR(1000), SORT INT)
AS
BEGIN
    DECLARE @CSTR        VARCHAR(MAX)    -- 남은 아이템 텍스트
    DECLARE @NSTR        VARCHAR(MAX)    -- 현재 아이템 텍스트
    DECLARE @SORT        INT             -- 정렬 순번
    
    -- STEP. 초기화
    SET @CSTR    = @INSTR
    SET @SORT    = 0
    
    -- STEP. LOOP 처리
    WHILE DATALENGTH(@CSTR) > 0
        BEGIN
            -- STEP. 순번 증가
            SET @SORT = @SORT + 1
            
            -- IF. 남은 항목 중 구분자가 있는 경우
            IF CHARINDEX(@KEY, @CSTR) > 0
                BEGIN
                    -- STEP. 항복 분리
                    SET @NSTR = SUBSTRING(@CSTR, 0, CHARINDEX(@KEY, @CSTR))                 -- 현재 항목
                    SET @CSTR = SUBSTRING(@CSTR, CHARINDEX(@KEY, @CSTR) + 1, LEN(@CSTR))    -- 남은 항목
                END
            ELSE
                -- CASE: 입력된 항목이 한 개인 경우
                BEGIN
                    -- STEP. 항복 분리
                    SET @NSTR = @CSTR    -- 현재 항목
                    SET @CSTR = NULL    -- 남은 항목
                END
            
            -- STEP. 결과 반환용 ARRAY에 추가
            INSERT @ARRAY (TXT, SORT) VALUES (@NSTR, @SORT)
        END
        
    -- STEP. 저장된 @ARRAY를 반환
    RETURN
END

 

SQL Server 2016부터는STRING_SPLIT() 함수가 추가되어 컬럼 문자열의 구분자를 행으로 분리할 수 있습니다.

STRING_SPLIT( "문자열", "구분자" )

 

 하지만, 이전 버전에서는 맨 위처럼 직접 프로시저를 생성해주어야 사용해야하합니다. 이 경우 아래와 같은 결과를 반환받아 볼 수 있습니다.

QUERY문과 결과화면

 

---

※ 동작 방식은 아래와 같습니다.

 

1/ 초기화

변수명	변수값
@CSTR	TEST1;TEST2;TEST3
@NSTR	NULL
@SORT	0

TXT	SORT

 

2/ Loop 시작

현재 @CSTR가 0byte 이상인 경우 계속 Loop

※ DATALENGTH란?  - 해당 문자열의 byte수 반환 ex) SELECT DATALENGTH('ASDF')     ▶4 ex) SELECT DATALENGTH('가나다라')     ▶8 영문은 1byte이고, 한글은 유니코드로 한 글자당 2byte이므로 총 4개의 문자임에도 각각 결과가 다름을 알 수 있습니다.

※ 여기에서는 문자열의 길이로도 충분하므로 아래와 같이 변경하여 사용하여도 됩니다.

 

3/ Loop #1

※ 현재 CHARINDEX(@KEY, @CSTR)은 CHARINDEX(';', 'TEST1;TEST2;TEST3')이므로 값은 6이며, IF문 안의 내용을 수행합니다.

※ CHARINDEX란?  - 주어진 문자에서 해당 키가 왼쪽 기준 맨 처음 만나는 키가 몇 번째 자리에 있는지 반환 사용방법 CHARINDEX(키, 문자열) 위의 예시에서  키          = 세미콜론(;) 문자열   = TEST1;TEST2;TEST3 왼쪽 기준 여섯 번째 자리에 세미콜론이 존재하므로, 결과값은 6을 반환하게 됩니다. (※ 순번이 1부터 시작 하는 것에 유의해야 하며, 일치하지 않는 경우 0을 반환합니다.)

CHARINDEX(';', 'TEST1;TEST2;TEST3')의 결과 값

 

변수명	변수값
@CSTR	TEST2;TEST3
@NSTR	TEST1
@SORT	1

 

 

TXT	SORT
TEST1	1

 

4/ Loop #2

※ 현재 CHARINDEX(@KEY, @CSTR)은 CHARINDEX(';', 'TEST2;TEST3')이므로 값은 6이며, IF문 안의 내용을 수행합니다.

CHARINDEX(';', 'TEST2;TEST3')의 결과 값

 

변수명	변수값
@CSTR	TEST3
@NSTR	TEST2
@SORT	2

 

TXT	SORT
TEST1	1
TEST2	2

 

5/ Loop #3

※ 현재 CHARINDEX(@KEY, @CSTR)은 CHARINDEX(';', 'TEST3')이므로 값은 0이며, ELSE문 안의 내용을 수행합니다.

CHARINDEX(';', 'TEST3')의 결과 값

 

변수명	변수값
@CSTR	NULL
@NSTR	TEST3
@SORT	3

 

TXT	SORT
TEST1	1
TEST2	2
TEST3	3

 

6/ 결과 반환