Separating a string

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  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

   

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  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 5 years ago by Phil Parkin.

  ---

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  July 16, 2020 at 12:11 pm

  #3770487

  thanks!!!