User Defined SPLIT Functions in SQL SERVER 2005

User Defined Function for Splitting the string value using the delimiter:
This is a function that requires the string value and the delimiter as input and returns a column of splitted values

CREATE FUNCTION [dbo].[Split]
(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([ID] BIGINT identity(1,1), [Value] nvarchar(4000))

BEGIN
DECLARE @NextString nvarchar(4000)
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @CommaCheck nvarchar(1)

--Initialize
SET @NextString = ''
SET @CommaCheck = right(@String,1)

--Check for trailing Delimiter, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
SET @String = @String + @Delimiter

--Get position of first Delimiter
SET @Pos = charindex(@Delimiter,@String) SET @NextPos = 1

--Loop while there is still a Delimiter in the String of levels
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT INTO @ValueTable ( [Value]) Values (@NextString)
SET @String = substring(@String,@pos +1,len(@String))
SET @NextPos = @Pos
SET @pos = charindex(@Delimiter,@String)
END
RETURN
END

For eg Executing the Query
SELECT [Value] FROM [dbo].[split]('1,2,3',',') will result in
table
Value
1
2
3
This function is useful when we pass a string list to a SP.