Welcome

Hello, Welcome to my blog. If you like feel free to refer others

Friday 7 October 2011

Function in Sql Server to break Comma-Separated Strings into Table

The below function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.SplitRowsIntoColumns(@InputString varchar(8000), @Delimiter char(1))      
    returns @temptable TABLE (items varchar(8000))      
    as      
    begin      
        declare @index int      
        declare @slice varchar(8000)      
         
        select @index = 1      
            if len(@InputString)<1 or @InputString is null  return      
         
        while @index!= 0      
        begin      
            set @index = charindex(@Delimiter,@InputString)      
            if @index!=0      
                set @slice = left(@InputString,@index - 1)      
            else      
                set @slice = @InputString      
             
            if(len(@slice)>0) 
                insert into @temptable(Items) values(@slice)      
     
            set @InputString = right(@InputString,len(@InputString) - @index)      
            if len(@InputString) = 0 break      
        end  
    return      
    end 

The above function can be called as :

select top 10 * from dbo.SplitRowsIntoColumns('Ashis,Rashmi,Vishal,Amit',',') 

The Output will be :


Happy learning....

No comments:

Post a Comment