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