Funtion to split any string contant in Microsoft SQL Server

After a very long time I am posting this stuff…

I have noticed that on day to day activity with MS SQL, one requires to split any string constant every now and then and so I have decided to make a post of it.

Please create below SQL function into your database and then you can use this to split any string constant with any separator you specified.


CREATE function [dbo].[fun_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

You can use this function like example given below:
SELECT Items FROM dbo.fun_Split('1,2,3,4',',')

This will return a table with Column “Items” where you can find each comma separated values in different rows.

Happy Scripting!