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

Prabhu Raja
13y
33k
0
0

Blog

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,

Split Function Result