How to split comma separated string in sql server

How to split comma separated string in sql server

this is the function i found after some Googling.
Which Tekes Two Arguments and Returns A table which contains all Values separated by comma in a Column of table.

(1)String Separated With some character.
(2)characted from where to split string.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitString]
(
@StringToSplit nvarchar(2000),
@SplitFrom nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitFrom,@StringToSplit)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@StringToSplit,1,Charindex(@SplitFrom,@StringToSplit)-1)))

Set @StringToSplit = Substring(@StringToSplit,Charindex(@SplitFrom,@StringToSplit)+1,len(@StringToSplit))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@StringToSplit))

Return
END


How To Call:


After CReating this function We can call it like a table.

select * from [dbo].[SplitString]('a,b,c',',')

you can Find More Details on

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Thanks.