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

Hi Everyone,

In most of the cases, we may like to send more then one input or value by one parameter. In those cases use the following technique. The main objective of the following Split function is to convert a comma-separated string value (‘abc,cde,fgh') into a temp table with each string as rows.

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


CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
   declare @idx int       
   declare @slice varchar(8000)       
      
   select @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)  
            insert into @temptable(Items) values(@slice)       
 
        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end 



And you can check this by,


select * from dbo.split( 'Chennai,Bangalore,Mumbai', ',' )



you will get output as,