Is it possible to parse key:value pairs in SQL

Question

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

Martin Smith · Accepted Answer · 2019-03-12T23:25:52.927

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);

edited Mar 12 '19 at 23:25

answered Mar 12 '19 at 23:20

Martin Smith

438,706
87
741
845

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

score 0 · Answer 2 · edited Aug 15 '23 at 08:34

SET NOCOUNT ON;
DECLARE
    @KeyValue nvarchar(1024),
    @Options nvarchar(1024)
         
SET Options =
'Action:DownloadDocuments|State:1|Site:250104|Ref:19014|Member:12345'

    DECLARE @KeyValuesTBL TABLE 
        (
            KeyName     nvarchar(80),
            KeyValue    nvarchar(1024)
        )

    INSERT INTO @KeyValuesTBL
    SELECT
            KeyName =
                SUBSTRING(value, 1, 
                ISNULL( NULLIF(CHARINDEX(':', value)-1, -1), 1024)
                )
            ,KeyValue =
            CASE CHARINDEX(':', value) 
                WHEN 0 THEN NULL
                ELSE SUBSTRING(value, CHARINDEX(':', value)+1, 1024)
            END
        FROM STRING_SPLIT(@Options, '|') WHERE (value <> '');
    SELECT * FROM @KeyValuesTBL;

    DELETE @KeyValuesTBL;

    SELECT @Options = 'StartDate=2023-08-14;Mode=1;Like=%TEST%;Flag=true'
    INSERT INTO @KeyValuesTBL
    SELECT
            KeyName =
                SUBSTRING(value, 1, 
                ISNULL( NULLIF(CHARINDEX('=', value)-1, -1), 1024)
                )
            ,KeyValue =
            CASE CHARINDEX('=', value) 
                WHEN 0 THEN NULL
                ELSE SUBSTRING(value, CHARINDEX('=', value)+1, 1024)
            END
        FROM STRING_SPLIT(@Options, ';') WHERE (value <> '');
    SELECT * FROM @KeyValuesTBL;

        SELECT @KeyValue = KeyValue
            FROM @KeyValuesTBL
            WHERE (KeyName = 'Flag');
        IF (@@ROWCOUNT <> 0) BEGIN
            SELECT ISNULL(TRY_CONVERT(bit, @KeyValue), 0) as Flag
        END;

Is it possible to parse key:value pairs in SQL

2 Answers2

Linked