MSSQL 문자열 구분자 분리 (테이블 반환 함수)

ALTER FUNCTION [dbo].[fn_Split]

(

    @pSource    VARCHAR(MAX),

    @pSeparator VARCHAR(1)

)

RETURNS @TAB_RETURN TABLE

(

    ItemIndex   INT,

    ItemValue   VARCHAR(1000)

)

AS

BEGIN

    DECLARE @buffer VARCHAR(MAX);

    DECLARE @item   VARCHAR(MAX);

     

    DECLARE @tmp   INT;

    DECLARE @index INT;

    SET @index = 1;

   

    WHILE LEN(@pSource) > 0

    BEGIN

        SET @tmp = CHARINDEX(@pSeparator, @pSource);

         

        IF @tmp > 0

        BEGIN

            SET @item    = SUBSTRING(@pSource, 1, @tmp - 1);

            SET @pSource = RIGHT(@pSource, LEN(@pSource) - @tmp);

            INSERT INTO @TAB_RETURN VALUES(@index, @item);

             

            SET @index = @index + 1;

        END ELSE

        BEGIN

            INSERT INTO @TAB_RETURN VALUES(@index, @pSource);

             

            BREAK;

        END;

    END;

      

    RETURN

END




출처 - http://marsland.tistory.com/236