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] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')