How to insert only a portion of the JSON element into a SQL field

Question

I'm using the following code in a SQL Server 2016 stored procedure and I would like to split the Journal ISSN value into 2 different fields.

DECLARE @json nVARCHAR(MAX) =
      '[{"Journal ISSN" : "15221059, 15309932"},
       {"Journal ISSN" : "23660058, 2366004X"}]'

INSERT INTO A_ADMIN_IMPACT_FACTORS_2020 ([Journal ISSN], [ISSN FirstEight],  [ISSN SecondEight_if Comma])
    SELECT [Journal ISSN]
    FROM OPENJSON(@json)
    WITH ([Journal ISSN] nvarchar(50))

I've tried to add

     SUBSTRING ([Journal ISSN],1,8) nvarchar(50)

in various spots but I'm sure the syntax is wrong.

Can someone point me in the right direction so that I end up with three columns?

One with the complete value
one with the first portion up the the comma or the entire value if no comma, and
one with what remains after the comma

As I was typing this out it occurred that I might be able to use a computed column, but I would still like to know how I can do it in the stored procedure.

score 0 · Answer 1 · answered May 05 '20 at 16:06

I got it;

     INSERT INTO A_ADMIN_IMPACT_FACTORS_2020 ([Journal ISSN], [ISSN Primary], [ISSN 
     Secondary])
     SELECT [Journal ISSN], SubString([Journal ISSN],1,8) as [ISSN Primary], CASE WHEN 
     CHARINDEX(',', [Journal ISSN]) > 1 THEN SubString([Journal ISSN], 11,8) ELSE '' 
     END AS [ISSN Secondary]
     FROM OPENJSON(@json)
     WITH ([Journal ISSN] nvarchar(50)

How to insert only a portion of the JSON element into a SQL field

1 Answers1