[SQL] SPLIT 함수

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
#####################################################################################
- 작성일 : 2008.04.29
- 작성내용 : SPLIT 함수구현
- 작성자 : 이현순
- 수정일 :
- 수정자 :
- 수정내용 :

SELECT * FROM dbo.FN_SPLIT('10,20,30,40,50',',')


######################################################################################
*/

ALTER FUNCTION [dbo].[FN_SPLIT] (@STRMORE AS VARCHAR(8000),@STRDELIMETER AS VARCHAR(10))



RETURNS @RETURN_TABLE TABLE

(

idx int identity(1,1)

, strVALUE VARCHAR(500)

)

AS

BEGIN



DECLARE @NINDEX INT
, @DEL_LENGTH INT
, @STRVALUE VARCHAR(1000)


SET @DEL_LENGTH = LEN(@STRDELIMETER)



WHILE LEN(@STRMORE) > 0

BEGIN

SET @NINDEX = CHARINDEX(@STRDELIMETER, @STRMORE)

IF (@NINDEX = 0)
BEGIN

INSERT @RETURN_TABLE (strVALUE) VALUES (@STRMORE)

RETURN

END

ELSE IF (@NINDEX = 1)

BEGIN

SET @STRMORE = SUBSTRING(@STRMORE, @DEL_LENGTH+1, LEN(@STRMORE))

CONTINUE

END


SET @STRVALUE = SUBSTRING(@STRMORE, 0, @NINDEX)

SET @STRMORE = SUBSTRING(@STRMORE, @NINDEX + @DEL_LENGTH, LEN(@STRMORE) - @NINDEX )

INSERT @RETURN_TABLE (strVALUE) VALUES (@STRVALUE) --, @STRMORE



END

RETURN

END