1

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
BlueChippy
  • 5,635
  • 16
  • 78
  • 129

1 Answers1

7

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);
Martin Smith
  • 419,657
  • 83
  • 708
  • 800
  • Where did the 8000 come from? – Nick May 15 '19 at 11:58
  • 1
    @Nick - just a large number that is hopefully larger than any `value` that may exist in practice. So that it reads until the end of the string without needing to bother doing anything with `LEN` to calculate the exact number of characters – Martin Smith May 15 '19 at 13:33