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?

John Cappelletti · Accepted Answer · 2019-02-17 14:50:45Z

1

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

answered Feb 17, 2019 at 14:50

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dhughes Over a year ago

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

John Cappelletti Over a year ago

@dhughes Happy to help. I learn something new every day here on SO... That's the fun part.

Collectives™ on Stack Overflow

Split Embedded CSV Text Into Separate Rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related