How to split comma separated value in sql server

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

Read More »