[MS-SQL] 문자열에서 구분자를 기준으로 분리하기

-- =============================================
-- Author:		marsland@hanmail.net
-- Create date: 2011-11-11
-- Description:	문자열 나누기
-- =============================================
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