Split Function in Sql Server to break Comma-Separated Strings into Table
Split a String into more then one value by using any delimiters.
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,