sql server – How to replace all instances of text between two characters in a huge JSON string using T-SQL in an Azure SQL database?

How to replace text between 2 characters MULTIPLE TIMES in a huge string (50,000 characters) in T-SQL?

I have a JSON output from my Azure Pipeline which we drop into a single cell in a table ready for the stored procedure to parse and process the data. However, one of the lines in the JSON looks like this

"body":"bxncucbuc "what is happening" cbibciuc",
"subject:"whatever" etc etc. .........  
"body":"bxncucbuc "what more things are happening" cbibciuc",
"subject:"whatever" etc etc. .........
"body":"bxncucbuc "what else is happening" cbibciuc",
"subject:"whatever" etc etc. 

The openjson cannot parse the script as the “what is happening” (or similar) looks like another column as it has the ” ‘s in. So, I am looking to replace everything BETWEEN “body”:” and “subject”:, as actually I don’t need this data anyway.

I can get a single instance replaced using

select   
    stuff(jsonresponse, CHARINDEX('"body":"', jsonresponse), CHARINDEX('"subject":', jsonresponse) - CHARINDEX('"body":"', jsonresponse) + 1, '"bodyX":"replaced with a random piece of text","')
from 
    stage.JsonResponse

However

while (select CHARINDEX('"body":"<', jsonresponse) from stage.JsonResponse) != 0
begin 
    update stage.JsonResponse 
    set JsonResponse =  stuff(jsonresponse, CHARINDEX('"body":"', jsonresponse), CHARINDEX('"subject":', jsonresponse) - CHARINDEX('"body":"', jsonresponse) + 1, '"bodyX":"replaced with a random piece of text","')
    from stage.Mimecast_JsonResponse

    if (select CHARINDEX('"body":"<', jsonresponse) 
        from stage.JsonResponse) = 0
        BREAK
    else
        CONTINUE
END  

just runs forever. I have changed it to look for “body”:”< in case it was inadvertently changing some script and then looking for itself, and changing that again etc.

Similarly, I have tried a CTE:

;WITH Cte(jsonresponse) AS 
(
    SELECT jsonresponse FROM #a
    UNION ALL
    SELECT STUFF(jsonresponse, CHARINDEX('"body":"', jsonresponse), CHARINDEX('"subject":"', jsonresponse) - CHARINDEX('"body":"', jsonresponse) + 1, '"body":"replaced with a random piece of text","')
    FROM Cte
    WHERE CHARINDEX('"body":"<', jsonresponse) != 0
)
SELECT jsonresponse 
FROM Cte
WHERE CHARINDEX('"body":"<', jsonresponse) = 0
OPTION (MAXRECURSION 0); `

but this too seems to run and run, but actually only changes the first instance.

My JSON is 50,000 characters long, and there are multiple instances in the json that I need to replace ALL OF THEM, and this is where I have struggled, how to change EVERY INSTANCE of the offending script.

Any help will be appreciated.

I am using T-SQL on an Azure SQL database.

Read more here: Source link