Split string with comma in SQL Server

Question

Please help how to split words in string with comma

Eg: for 'abcdef'

Output : 'a,b,c,d,e,f'

Don't do that in SQL, do it in code in the presentation layer. — Zohar Peled, May 13 '15 at 07:16
@Umesh, You should catch up with the lingo. splitting a string is not the same as injecting it with characters. — Zohar Peled, May 13 '15 at 07:20

Giorgos Betsos · Answer 1 · 2015-05-13T07:18:21.610

5

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.

edited May 13 '15 at 07:18

answered May 13 '15 at 07:14

Giorgos Betsos

71,379
9
63
98

1

I would Add a link to msdn on stuff function. still got my vote, though. – Zohar Peled May 13 '15 at 07:16

score 3 · Answer 2 · edited May 23 '17 at 12:14

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:

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.

score 1 · Answer 3 · answered May 13 '15 at 07:52

1

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

answered May 13 '15 at 07:52

t-clausen.dk

43,517
12
59
92

score 0 · Answer 4 · answered May 13 '15 at 07:13

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')

score 0 · Answer 5 · answered May 13 '15 at 07:14

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

score 0 · Answer 6 · answered May 13 '15 at 08:28

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. :)

score 0 · Answer 7 · answered May 13 '15 at 10:55

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

Split string with comma in SQL Server

7 Answers7