Separating a string

  • 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

     

  • 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 6 months, 1 week ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • thanks!!!

     

     

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

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