Post reply

Separating a string

  • July 15, 2020 at 8:30 pm

    #3770363

    Hi,

    I have a string that comes into one of my column and I need to create a column for each item.

    It looks something like this but much longer

    {"Id":"1bban5d82-23g4c-430ui-b804-4c3awasgec28","RecordType":20,"CreationTime":"2020-07-07T03:39:55"}

    Thanks

    Astrid

     

  • July 15, 2020 at 9:57 pm

    #3770403

    After adding [] to make this valid JSON, I came up with this

    DROP TABLE IF EXISTS #SomeJSON;

    CREATE TABLE #SomeJSON
    (
        JSONString VARCHAR(MAX) NOT NULL
    );

    INSERT #SomeJSON
    (
        JSONString
    )
    VALUES
    ('[{"Id":"1bban5d82-23g4c-430ui-b804-4c3awasgec28","RecordType":20,"CreationTime":"2020-07-07T03:39:55"}]');

    SELECT sj.JSONString
          ,x.id
          ,x.RecordType
          ,x.CreationTime
    FROM #SomeJSON sj
        CROSS APPLY
    (
        SELECT *
        FROM
            OPENJSON(sj.JSONString)
            WITH
            (
                id VARCHAR(100) 'strict $.Id'
               ,RecordType INT '$.RecordType'
               ,CreationTime DATETIME '$.CreationTime'
            )
    ) x;

    • This reply was modified 4 years, 5 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 16, 2020 at 12:11 pm

    #3770487

    thanks!!!

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply