Wednesday, April 6, 2011

Create function to Split Comma Seperated string and Return Table in Sql Server

Below is Code

ALTER FUNCTION [dbo].[SplitString] (
@String varchar(4000),
@Delimiter char(1)
) RETURNS @tmp TABLE(ProductId int,Quantity int)

AS 
BEGIN
   DECLARE @idx INT
   DECLARE @slice VARCHAR(4000)
   set @idx = 1
   IF len(@String)<1 or @String is null
        return
   WHILE @idx!= 0
   BEGIN
           set @idx = charindex(@Delimiter,@String)
           IF @idx!=0
                set @slice = left(@String,@idx - 1)
           ELSE
                 set @slice = @String
          IF len(@slice)>0
          BEGIN
                  declare @first int
                  declare @second int
                  declare @index int
                  set @index=charindex(':',@slice)
                  set @first=convert(int,left(@slice,@index - 1))
                  set @second=convert(int,right(@slice,len(@slice)-@index))
                  insert into @tmp(ProductId,Quantity)
                     values(@first,@second)
          END
          set @String = right(@String,len(@String) - @idx)
         IF len(@String) = 0
                  break
    END
    RETURN
END

No comments:

Post a Comment