reading JSON values .. in SQL 2012

I have a SQL Server 2012 table with a column as per:

ResponseJSON varchar(max)

It contains text values like

{
  "partNumber": 1,
  "partTotal": 1,
  "payeeLocationId": "ABC00011",
  "remittanceAdviceId": "77592",
  "paymentInfo": {
    "accountInfo": {
      "accountName": "ABC Hospital",
      "xyzNumber": "",
      "xyzCode": ""
    },
    "depositAmount": "1234",
    "paymentReference": "ERA 1234"
  },
  "paymentRun": {
    "payerName": "ABC",
    "runDate": "2022-12-05"
  },
  "claimSummary": [
    {
      "benefit": "5555",
      "channelCode": "ABC",
      "claimId": "1234",
      "lodgementDate": "2022-02-14",
      "transactionId": "xpxpxpxpxxp",
      "accountReferenceId": "12345678"
    }
  ]
}

I wondered how to read the remittanceAdviceId value of 77592 (in this case) out of this JSON column data .. The remittanceAdviceId may be varying size in length .. e.g. 1,2,3,4,5,6,7 etc digits

I considered something like :

SELECT remittanceAdviceId = CASE
         WHEN E.RequestJSON IS NOT NULL AND
              CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) > 0 THEN
          SUBSTRING(E.RequestJSON,
                    CHARINDEX('"remittanceAdviceId"', E.RequestJSON, 0) + 22,
                    5)
         ELSE
          NULL
       END

but this isn't quite right as value may be other than 5 digits ..

Solution

Assuming upgrading to the latest version of SQL isn't in the cards right now, here's a simple approach using SUBSTRING and CHARINDEX:

DECLARE @json varchar(2000) = '{
  "partNumber": 1,
  "partTotal": 1,
  "payeeLocationId": "ABC00011",
  "remittanceAdviceId": "77592",
  "paymentInfo": {
    "accountInfo": {
      "accountName": "ABC Hospital",
      "xyzNumber": "",
      "xyzCode": ""
    },
    "depositAmount": "1234",
    "paymentReference": "ERA 1234"
  },
  "paymentRun": {
    "payerName": "ABC",
    "runDate": "2022-12-05"
  },
  "claimSummary": [
    {
      "benefit": "5555",
      "channelCode": "ABC",
      "claimId": "1234",
      "lodgementDate": "2022-02-14",
      "transactionId": "xpxpxpxpxxp",
      "accountReferenceId": "12345678"
    }
  ]
}';

SELECT SUBSTRING (
    @json
    , CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) + 23
    , CHARINDEX ( '",', @json, CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) ) - CHARINDEX ( '"remittanceAdviceId": "', @json, 0 ) - 23
) AS remittanceAdviceId;

RETURNS

+--------------------+
| remittanceAdviceId |
+--------------------+
|              77592 |
+--------------------+

NOTES

Assumes valid JSON with quoted values.
There is no need to specify a length for the remittance id. It will get parsed accordingly.

UPDATE

Now that you know you can use the native JSON feature in SQL, the simplest way to extract a single value from JSON is:

SELECT JSON_VALUE ( @json, '$.remittanceAdviceId' ) AS remittanceAdviceId;

RETURNS

+--------------------+
| remittanceAdviceId |
+--------------------+
|              77592 |
+--------------------+