SQL Server Remove JSON Node Using Regex

Starting with SQL Server 2016 you may use built-in JSON support, but you can't remove JSON items from JSON array using regex. One possible approach in this case is to parse the JSON using OPENJSON and get content as table, filter the table with the appropriate WHERE clause and format table rows as JSON using FOR JSON:

Table:

CREATE TABLE Data (JsonColumn varchar(1000))
INSERT INTO Data (JsonColumn)
VALUES ('[
   {"A":"957d43b6-07ce-41b4-b7b8-a6098ca08bba","RN":0,"N":"ABC 1%","R":10.00,"T":"b57d51ef-5538-4725-82eb-0f2fad4f1b82"},
   {"A":"99f7cd78-54c3-40cb-bde4-21f1b826ad86","RN":0,"N":"ABC 1%","R":10.00,"T":"294ac06a-1cd1-4961-8298-6e3b63ebd979"}
]')

Statement:

UPDATE Data
SET JsonColumn = (
   SELECT *
   FROM OPENJSON(JsonColumn) WITH (
      A varchar(50) '$.A',
      RN int '$.RN',
      N varchar(10) '$.N',
      R numeric(5, 2) '$.R',
      T varchar(50) '$.T'
   )
   WHERE A <> '99f7cd78-54c3-40cb-bde4-21f1b826ad86'
   FOR JSON PATH
)

Result:

JsonColumn
[{"A":"957d43b6-07ce-41b4-b7b8-a6098ca08bba","RN":0,"N":"ABC 1%","R":10.00,"T":"b57d51ef-5538-4725-82eb-0f2fad4f1b82"}]