Split Function for MS SQL Server

A custom MS SQL Server function to split a string input and return an array:

Just run the below function in ur database:

CREATE function ReturnArray (@String varchar(4000), @Identifier nvarchar(5))
Returns @tbl Table(Value varchar(4000))
as
Begin
Declare @Index int, @Str nvarchar(4000), @TempStr nvarchar(4000)
Select @Str = @String
Select @Index = 0
Select @Identifier = '%'+@Identifier+'%'

While (PatIndex(@Identifier, @Str) > 0)
Begin
Select @Index = PatIndex(@Identifier, @Str)
Select @TempStr = Left(@Str, @Index-1)
Insert into @tbl Select @TempStr
Select @Str = SubString(@Str, PatIndex(@Identifier, @Str) + 1, Len(@Str) - @Index)
End
Insert into @tbl values(@str)
Return
End

This function expects 2 parameters as input :
1. string -- string to be split
2. seperator -- separator used ie(in 'D,i,n,e,s,h', ',' is the separator)

Select value from dbo.ReturnArray('D,i,n,e,s,h', ',') will return all the split values.