Function to split string in SQL

Hello

I have found one interesting function using which you can split the text with some character like ',' or any other character.


CREATE function [dbo].[Split] (@String nvarchar(4000), @Delimiter char(1))
Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)

Select @Index = 1
If @String Is NULL Return

While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If @Index <> 0

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End
Return
End


Now how to use this function

select * from dbo.Split('a,b,c,d',',')

this will give following result

Items
-----
a
b
c
d


How this function can make your life easy!

Suppose if you want to insert more than one row in a table depending on above function result

you can just do as follows

insert into Table_Name (Column_Name)
select * from dbo.Split('a,b,c,d',',')


Enjoy