[MSSQL] Split 사용자 함수 프로그램

/*
SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')
*/
CREATE FUNCTION [dbo].[FN_SPLIT]
(
    @PARAM VARCHAR(MAX) -- 대상문자열
    , @DIV VARCHAR(1) -- 특정구분자
)
RETURNS
@RESULT TABLE
(
    [VALUE] VARCHAR(MAX)
    , [ORDER] INT
)
AS
 
BEGIN
 
 
    DECLARE @INDEX INT
    DECLARE @POS INT
    DECLARE @ORDER INT
    DECLARE @TEMP VARCHAR(MAX)
 
 
 
    SET @INDEX = 1
    SET @POS = 1
    SET @ORDER = 1
 
 
 
    WHILE @POS > 0
    BEGIN
        SET @POS = CHARINDEX(@DIV, @PARAM, @INDEX)
 
 
 
        IF @POS = 0
        BEGIN
            SET @TEMP = RIGHT(@PARAM, LEN(@PARAM) - @INDEX + 1)
        END
        ELSE
        BEGIN
            SET @TEMP = SUBSTRING(@PARAM, @INDEX, @POS - @INDEX)
        END
 
 
 
        IF LEN(@TEMP) > 0
        BEGIN
            SET @TEMP = RTRIM(LTRIM(@TEMP))
            INSERT INTO @RESULT VALUES (@TEMP, @ORDER)
        END
 
 
 
       SET @INDEX = @POS + 1
       SET @ORDER = @ORDER + 1
 
 
    END
 
RETURN
END

--실행결과--
SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')

출처: http://mdj1234.tistory.com/38[짬타이거 화이팅!]