Search code examples
sql-serversplit

Is it possible to parse key:value pairs in SQL

I have a database column (varchar) that holds data in the following format:

Action:DownloadDocuments|State:1|Site:250104|Ref:19014|Member:12345

I know I can use STRING_SPLIT to get pairs of key:value but I'm trying to get a table of keys and values. I've tried UNIONS of splittings, but so far just get errors

I've tried

        ;WITH cte_pairs AS
(
SELECT value
FROM STRING_SPLIT(@activityValue, '|')
)
, cte_keyvalues AS
(SELECT value
FROM STRING_SPLIT(cte_pairs.value,':')
)
SELECT * FROM cte_keyvalues
Solution

  • Yes, this is quite simple

    SELECT [key] = LEFT(s.value, ca.pos - 1),
       [value] = SUBSTRING(s.value, ca.pos + 1, 8000)
FROM STRING_SPLIT(@activityValue, '|') s
CROSS APPLY (VALUES(CHARINDEX(':', s.value))) ca(pos);