Comma Seperater

Comma Seperater

--- Create Function
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

--- drop function dbo.Split

---

-- Test 1
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
-- Test 2
--select * from [dbo].[UDF_SPLIT]('Chennai,Bangalore,Mumbai',',')

---

---Insert into Temp Table
create table #temptable (id int identity(1,1),col1 varchar(255))
insert into #temptable
select * from dbo.split('Chennai,Bangalore,Mumbai',',')
select * from #temptable

---

---Another Method
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION SplitForDelimiter
(
@delimiter VARCHAR(10),
@input VARCHAR(1000)
)
RETURNS @tempTable TABLE(
data VARCHAR(100)
)
BEGIN
DECLARE @tempstr VARCHAR(1000)
SET @tempstr = @input
WHILE(charindex(@delimiter,@tempstr,0) > 0)
BEGIN
DECLARE @t VARCHAR(100)
SET @t = Substring(@tempstr,0,(charindex(@delimiter,@tempstr,0)))
INSERT into @tempTable (data) VALUES (@t)
SET @tempstr = Substring(@tempstr,charindex(@delimiter,@tempstr,0)+1,Len(@tempstr))
if(charindex(@delimiter,@tempstr,0) <=0)
BEGIN
INSERT into @tempTable (data) VALUES (@tempstr)
END
END
RETURN
END

---