How to split comma separated value in SQL server :
----- Approach 1: Common Table Expression (CTE) ----- Lets call this function as Split2. here we are using
CREATE FUNCTION dbo.Split2 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
begin
WITH StrCTE(start, stop) AS
(
SELECT 1, CHARINDEX(',' , @strString )
UNION ALL
SELECT stop + 1, CHARINDEX(',' ,@strString , stop + 1)
FROM StrCTE
WHERE stop > 0
)
insert into @Result
SELECT SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM StrCTE
return
end
GO
----- Approach 2: XML (surprise) ----- XML could be applied to do some type of string parsing (see this) Let’s call this function as Split3.
CREATE FUNCTION dbo.Split3 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
BEGIN
DECLARE @x XML
SELECT @x = CAST('
'+ REPLACE(@strString,',','')+ '' AS XML)
INSERT INTO @Result
SELECT t.value('.', 'int') AS inVal
FROM @x.nodes('/A') AS x(t)
RETURN
END
GO
----- Approach 3: Classic TSQL Way ----- This approach is slightly unusual but very effective. this needs you to create a table of sequential numbers called a Tally Table.
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
----- Lets index the table for better performance. -----
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
----- Finally out Split4 function. -----
CREATE FUNCTION dbo.Split4 ( @strString varchar(4000))
RETURNS @Result TABLE(Value BIGINT)
AS
BEGIN
SET @strString = ','+@strString +','
INSERT INTO @t (Value)
SELECT SUBSTRING(@strString,N+1,CHARINDEX(',',@strString,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@strString)
AND SUBSTRING(@strString,N,1) = ',' --Notice how we find the comma
RETURN
END
GO
----- so I changed the code slightly to test out XML function directly and copied data ia a temporary table -----
--declare @str varchar(4000) = '1,2,3,4,5,6'
declare @str varchar(4000) = 'A,B,C,D,E,F'
declare @xml XML
select @xml = cast('
'+REPLACE(@str,',','')+'' as XML)
select * into tempTable from (select t.value('.', 'VARCHAR') as inVal
from @xml.nodes('/A') as x(t)) as tbl
select * from tempTable
----- so I changed the code slightly to test out XML function directly and copied data using Table Variable Parameter -----
--declare @str varchar(4000) = '1,2,3,4,5,6'
declare @temp table(VALUE nvarchar(100))
declare @str varchar(4000) = 'A,B,C,D,E,F'
declare @xml XML
select @xml = cast('
'+REPLACE(@str,',','')+'' as XML)
insert into @temp select t.value('.', 'VARCHAR') as inVal
from @xml.nodes('/A') as x(t)
select * from @temp
