Split string with comma in SQL Server

If you want to operate on a tsql variable:

DECLARE @str VARCHAR(40) = 'abcdef'

you can use the following code that uses spt_values to create a tally and STUFF to inject , characters in the string:

SELECT @str = STUFF(@str, Number * 2, 0, ',')
FROM [master].[dbo].[spt_values]
WHERE Type = 'P' AND
      Number BETWEEN 1 AND LEN(@str) - 1

After the execution of the above, @str is a,b,c,d,e,f.

This is really not best handled in pure SQL and would be more suited to an application tier, or CLR, but one solution would be to split out your string into its individual components, then rebuild it with a comma separator. To do this first you need a numbers table, the following series goes into some depth about the best way of doing this:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

For the sake of the answer I will assume you have no numbers table, and need to create one on the fly, and the most efficient way to do this is using stacked CTEs. The following will return a list from 1 to 10000:

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)
SELECT  *
FROM    Numbers;

Then you can use these numbers to split your string:

DECLARE @T TABLE (Col VARCHAR(10));
INSERT @T VALUES ('abcdef');

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)

SELECT  *,
        Letter = SUBSTRING(t.Col, n.Number, 1)
FROM    @T AS t
        INNER JOIN Numbers n
            ON n.Number <= LEN(t.Col);

This gives you:

Col     Number  Letter
------------------------
abcdef  1       a
abcdef  2       b
abcdef  3       c
abcdef  4       d
abcdef  5       e
abcdef  6       f

Then you can rebuild your string using SQL Servers XML extensions:

DECLARE @T TABLE (Col VARCHAR(10));
INSERT @T VALUES ('abcdef'), ('test');

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)

SELECT  t.Col,
        Split = (   SELECT  CASE WHEN n.Number = 1 THEN '' ELSE ',' END + SUBSTRING(t2.Col, n.Number, 1)
                    FROM    @T AS t2
                            INNER JOIN Numbers n
                                ON n.Number <= LEN(t2.Col)
                    WHERE   t2.Col = t.Col
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    @T AS t;

The benefit of this method is that you can use it as an inline table valued function:

CREATE FUNCTION dbo.InjectDelimiter (@String VARCHAR(1000), @Delimiter CHAR(1))
RETURNS TABLE 
AS
RETURN 
(   WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
    N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
    N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
    Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)
    SELECT  Split = (   SELECT  CASE WHEN n.Number = 1 THEN '' ELSE @Delimiter END + SUBSTRING(@String, n.Number, 1)
                        FROM    Numbers n
                        WHERE   n.Number <= LEN(@String)
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
);

Then you can call it as:

SELECT  t.Name, i.Split
FROM    sys.tables AS t
        CROSS APPLY dbo.InjectDelimiter(t.name, ',') AS i;

Which will perform much better than a scalar function if you need to call it on multiple rows.

I would use a while loop:

DECLARE @str VARCHAR(max) = 'abcdef'

DECLARE @loop INT = LEN(@str)

WHILE @loop > 1
  SELECT @str = STUFF(@str, @loop, 0, ','), @loop -= 1

SELECT @str

You can create the following function for this

CREATE FUNCTION PutCommasBetweenChars 

   (@String VARCHAR(100))

RETURNS VARCHAR(100)
AS
BEGIN
   DECLARE @pos INT, @result VARCHAR(100); 
   SET @result = @String; 
   SET @pos = 2 -- location where we want first space 
   WHILE @pos < LEN(@result)+1 
   BEGIN 
       SET @result = STUFF(@result, @pos, 0, ','); 
       SET @pos = @pos+2; 
   END 
   RETURN @result; 
END
GO

And execute it as follows

print dbo.PutCommasBetweenChars('abcdef')

A time ago I came up with this solution :

Declare @separator as nvarchar(1)= ','
Declare @filterlist as nvarchar(MAX) = '1,2,3,4,5'

IF OBJECT_ID('tempdb..#filterList') IS NOT NULL
DROP TABLE #filterlist

--Create temporary filter list table
create table #FilterList (
filter varchar(100) Not NULL
)

--Add 1 comma to the filter list, used for processing the list
set @filterlist = @filterList + @separator 

--Declare and set default variable values for processing Filter list
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(100)

set @pos = 0
set @len = 0

--Loop thru the string of filter list, separate the , values and insert into the #Filterlist
       WHILE CHARINDEX(@separator , @filterList, @pos+1)>0

    BEGIN

        set @len = CHARINDEX(@separator , @filterList, @pos+1) - @pos
        set @value = SUBSTRING(@filterList, @pos, @len)

        insert Into #FilterList (filter) Values(@value)

        set @pos = CHARINDEX(@separator , @filterList, @pos+@len) + 1

    END

    select * from #FilterList 

After spend some time I have found your solutions

DECLARE @Chars VARCHAR(8000) 

SELECT  @Chars = COALESCE(@Names + ', ', '') + Main.SplitChar
FROM 
(

select (substring(a.b, v.number+1, 1)) AS SplitChar
from (select 'QWERTY' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'
) AS MaIn

SELECT @Chars

Please let me know, is it working or not. :)

Thank you all ! I tried below code, but I got better answers from you all guys

CREATE TABLE #TEMP (ID INT IDENTITY, CHARR VARCHAR(MAX))
DECLARE @DATA NVARCHAR(MAX)='ABCDEFG'

DECLARE @LEN INT = LEN(@DATA)
DECLARE @INT INT = 1
WHILE @INT<=@LEN
BEGIN 
INSERT INTO #TEMP VALUES (SUBSTRING(@DATA,@INT,1))
SET @INT=@INT+1
end

SELECT STUFF(
(SELECT ',' + S.CHARR
FROM #TEMP s
ORDER BY s.ID
FOR XML PATH('')),1,1,'') AS CSV