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

The below Split function is Table-valued function which would help us 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

split function can be Used as
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
would return

Above Split function can be used to pass parameter to IN clause in sql server.