Split Embedded CSV Text Into Separate Rows

Question

I'm trying to split comma-separated data into separate columns (using SQL Server 2008). I've seen similar questions on here, but in my case, the data contained in each row is BOTH comma-delimited and carriage-return-delimited.

EXAMPLE:

Date              Content
----              -------
1/1/2019          1, John, Doe
                  2, Jane, Doe

1/2/2019          1, John, Doe
                  2, Jane Doe
                  3, Mary, Smith

Using a custom split function, I was able to return the value for just one record:

SELECT * FROM Split_CTE((SELECT TOP 1 content FROM myTable), CHAR(10))

RESULT:

1,John,Doe
2,Jane,Doe

And using a substring function, I was able to return just the first row from each record:

SELECT dateRetrieved, SUBSTRING(content, 1, CHARINDEX(CHAR(10), content)-1) as Row FROM myTable

RESULT:

1/1/2019    1,John,Doe
1/2/2019    1,John,Doe

But what I'm trying to get back is this:

Date     Row   First   Last
1/1/2019 1     John    Doe
1/1/2019 2     Jane    Doe
1/2/2019 1     John    Doe
1/2/2019 2     Jane    Doe
1/2/2019 3     Mary    Smith

Any suggestions?

score 1 · Accepted Answer · answered Feb 17 '19 at 14:50

Cross Apply B will split using the CRLF delimiter

Cross Apply C with parse the comma delimited string from B into columns

I should note that each of the CROSS APPLY's could be converted into a TVF

Example

Select A.Date
      ,C.*
 From  YourTable A
 Cross Apply (
                Select RetSeq = row_number() over (order by 1/0)
                      ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace([Content],char(13)+char(10),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(B.RetVal,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) C
 Order by A.Date,C.Pos1

Returns

Date        Pos1    Pos2    Pos3
2019-01-01  1       John    Doe
2019-01-01  2       Jane    Doe
2019-01-02  1       John    Doe
2019-01-02  2       Jane    Doe
2019-01-02  3       Mary    Smith

Brilliant thanks - that did the trick! I tend to get lost when it comes to cross applies - I need to invest in an online tutorial on the advanced features of SQL. The built-in tools available to coerce data into seemingly endless formats never ceases to amaze me... — dhughes, Feb 17 '19 at 19:11
@dhughes Happy to help. I learn something new every day here on SO... That's the fun part. — John Cappelletti, Feb 17 '19 at 19:14

Split Embedded CSV Text Into Separate Rows

1 Answers1