Parse a comma delimited string into multiple rows

Question

I have a string

('emp_id="123",emp_status="New Hire",emp_name="Smith, John A",emp_salary="2000",emp_state="VA"')

How do I split this into

Column Name  Value
emp_id       123
emp_status   New Hire
emp_name     Smith, John A (Note: This has a comma within the string)
emp_salary   2000
emp_state    VA

Possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Prisoner, Jan 24 '17 at 04:33
Plus, the question lacks any real attempt at a solution. If you arent going to perform expect nothing here. — clifton_h, Jan 24 '17 at 04:39
Best option is to *not* use somma separated values, use a many-to-many table or a TVP. SQL Server 2016 added the [STRING_SPLIT](https://msdn.microsoft.com/en-us/library/mt684588.aspx) command as a convenience that shouldn't be abused — Panagiotis Kanavos, Jan 24 '17 at 08:39
What you ask isn't splitting, it's actually parsing a string to generate key-value pairs. That should be done on the client's side. Such things shouldn't be stored in the database or parsed with T-SQL — Panagiotis Kanavos, Jan 24 '17 at 08:40
Not really a duplicate, the OP is asking to a multi-column table, this is more parsing then spliting, as Panagiotis Kanavos wrote. — ATC, Jan 25 '17 at 12:33

score 1 · Answer 1 · answered Jan 24 '17 at 08:36

One way to solve this is using a recursive CTE. It's probably not the fastest way, but it's the simplest way I've found in this particular situation:

DECLARE @Source varchar(200) = 'emp_id="123",emp_status="New Hire",emp_name="Smith, John A",emp_salary="2000",emp_state="VA"'

;WITH CTE AS
(
    SELECT LEFT(@Source, CHARINDEX('",', @Source)) as val, 
           RIGHT(@Source, LEN(@Source) - CHARINDEX('",', @Source)-1) as string 

    UNION ALL

    SELECT  CASE WHEN CHARINDEX('",', string) > 0 THEN
                LEFT(String, CHARINDEX('",', string))
            ELSE
                String
            END  as val,
            CASE WHEN CHARINDEX('",', string) > 0 THEN
                RIGHT(string, LEN(string) - CHARINDEX('",', string)-1) 
            ELSE
                ''
            END as string
    FROM CTE
    WHERE LEN(string) > 0
)

SELECT LEFT(val, CHARINDEX('=', val)-1) As [Column Name], 
       REPLACE(RIGHT(val, LEN(val) - CHARINDEX('=', val) -1 ), '"', '') As Value
FROM CTE

Results:

Column Name     Value
emp_id          123
emp_status      New Hire
emp_name        Smith, John A
emp_salary      2000
emp_state       VA

Thank you Zohar. It seems to work but does not parse if the value after the = sign is not enclose din double quotes Ex:('emp_reg_num=888,emp_status="New Hire",emp_name="Smith, John A",emp_salary="2000",emp_state="VA") — SQL User, Jan 27 '17 at 00:15
It doesn't parse the values not enclosed in double quotes, if they are at the beginning of the string or in the middle of the string or at the end of the string — SQL User, Jan 27 '17 at 00:16
And for that you should use a csv parser. It's not an easy thing ro do in sql. — Zohar Peled, Jan 27 '17 at 05:19

Parse a comma delimited string into multiple rows

1 Answers1