Friday, November 6, 2009

comma separate in sql

CREATE FUNCTION dbo.fnc_Split
(
@Data varchar(2000),
@Sep varchar(5)
)
RETURNS @Temp table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@Sep,@RData)>0)
Begin
Insert Into @Temp(data)
Select
Data = ltrim(rtrim(Substring(@Data,1,Charindex(@Sep,@Data)-1)))

Set @Data = Substring(@Data,Charindex(@Sep,@Data)+1,len(@Data))
Set @Cnt = @Cnt + 1
End

Insert Into @Temp (data)
Select Data = ltrim(rtrim(@Data))

Return
END