Split (SQL Server 2005)

 UDF Split:

Input String: 1,2,3,4

Output: 1

2

3

4

CREATE  Function Split(@StrValue varchar(7999)) Returns 

@splt Table (StrVal Nvarchar(225))

As

Begin

Declare @SubTbl Table (MyVal Nvarchar(225))

Declare @SrLoop Int

Declare @CutStr nVarchar(225)

Set @SrLoop = 1

While Len(@StrValue) >= @SrLoop

Begin

Set @CutStr = SUBSTRING ( @StrValue ,  @SrLoop, (Case CHARINDEX ( ',' , @StrValue , @SrLoop) When 0 Then Len(@StrValue)+1 Else CHARINDEX ( ',' , @StrValue , @SrLoop) End) - @SrLoop)

Insert Into @SubTbl (MyVal) Values (LTrim(RTrim(@CutStr)))

Set @SrLoop = (Case CHARINDEX ( ',' , @StrValue , @SrLoop) When 0 Then Len(@StrValue)+1 Else CHARINDEX ( ',' , @StrValue , @SrLoop) End) + 1

End

Insert Into @splt (StrVal) Select MyVal From @SubTbl

Return

End