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 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.

  • July 16, 2020 at 12:11 pm

    #3770487

    thanks!!!

     

     

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

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