Split Comma Separated String Table Row into Separate Rows Using Tsql

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

Split comma separated string table row into separate rows using TSQL

Alternatively, you could use XML like so:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e,f,g');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)

Split one row into multiple rows based on comma-separated string column

Use unnest on the array returned by split.

SELECT a,split_b 
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

Split comma separated values into target table with fixed number of columns

It is typically bad design to store CSV values in a single column. If at all possible, use an array or a properly normalized design instead.

While stuck with your current situation ...

For known small maximum number of elements

A simple solution without trickery or recursion will do:

SELECT id, 1 AS rnk
     , split_part(csv, ', ', 1) AS c1
     , split_part(csv, ', ', 2) AS c2
     , split_part(csv, ', ', 3) AS c3
     , split_part(csv, ', ', 4) AS c4
     , split_part(csv, ', ', 5) AS c5
FROM   tbl
WHERE  split_part(csv, ', ', 1) <> '' -- skip empty rows

UNION ALL
SELECT id, 2
     , split_part(csv, ', ', 6)
     , split_part(csv, ', ', 7)
     , split_part(csv, ', ', 8)
     , split_part(csv, ', ', 9)
     , split_part(csv, ', ', 10)
FROM   tbl
WHERE  split_part(csv, ', ', 6) <> '' -- skip empty rows

-- three more blocks to cover a maximum "around 20"

ORDER  BY id, rnk;

db<>fiddle here

id being the PK of the original table.

This assumes ', ' as separator, obviously.

You can adapt easily.

Related:

• Split comma separated column data into additional columns

For unknown number of elements

Various ways. One way use regexp_replace() to replace every fifth separator before unnesting ...

-- for any number of elements
SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 1) AS c1
     , split_part(c.csv5, ', ', 2) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 4) AS c4
     , split_part(c.csv5, ', ', 5) AS c5
FROM   tbl t
     , unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER  BY t.id, c.rnk;

db<>fiddle here

This assumes that the chosen separator ; never appears in your strings. (Just like , can never appear.)

The regular expression pattern is the key: '((?:.*?,){4}.*?),'

(?:) ... “non-capturing” set of parentheses

() ... “capturing” set of parentheses

*? ... non-greedy quantifier

{4}? ... sequence of exactly 4 matches

The replacement '\1;' contains the back-reference \1.

'g' as fourth function parameter is required for repeated replacement.

Further reading:

• PostgreSQL & regexp_split_to_array + unnest
• Apply `trim()` and `regexp_replace()` on text array
• PostgreSQL unnest() with element number

Other ways to solve this include a recursive CTE or a set-returning function ...

Fill from right to left

(Like you added in How to put values starting from the right side into columns?)

Simply count down numbers like:

SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 5) AS c1
     , split_part(c.csv5, ', ', 4) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 2) AS c4
     , split_part(c.csv5, ', ', 1) AS c5
FROM ...

db<>fiddle here

How to split the row by a comma and unpivot the table in PostgreSQL?

you can use string_to_array() and unnest()

SELECT t.birthday_id , 
       t.child_birthday_id,
       p.place
FROM sh.test t
  CROSS JOIN LATERAL unnest(string_to_array(place, ',')) as p(place);

If you are using Postgres 14 you can also use string_to_table(t.place, ',')

How to split comma delimited data from one column into multiple rows

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

SELECT t.Employee_FullName,
       Code = LTRIM(j.value),
       Hours = MAX(CASE j.[key] 
                   WHEN 0 THEN RegularTime
                   WHEN 1 THEN DoubleTime
                   WHEN 2 THEN Overtime END)
  FROM dbo.MyTable AS t
  CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
  GROUP BY t.Employee_FullName, LTRIM(j.value);

• Example db<>fiddle

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

---

Related Topics