By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab ( JSONDetail VARCHAR(2000) );
INSERT INTO tab VALUES
('{
"noteId": "a34f17c4-f4fd-45ea-b4da-732ef8126a6b",
"memberName": "Test LINKOUS",
"tenantId": "548bead1-bdab-e811-bce7-0003ff21d46b",
"noteType": "General Note",
"memberId": "84cf0adb-850d-e711-80c8-000d3a103f46",
"createdOn": "2020-09-13T17:47:33.2864868Z",
"comment": "test "word" test",
"contacts": [
{
"otherContactType": "",
"communicationType": ""
}
]
}');
INSERT INTO tab VALUES
('{
"noteId": "b45f17c4-g4gd-45ea-b4da-843ef8126b6c",
"memberName": "Test LINKOUS",
"tenantId": "548bead1-bdab-e811-bce7-0003ff21d46b",
"noteType": "General Note",
"memberId": "84cf0adb-850d-e711-80c8-000d3a103f46",
"createdOn": "2020-09-13T17:47:33.2864868Z",
"comment": "test word test",
"contacts": [
{
"otherContactType": "",
"communicationType": ""
}
]
}');
2 rows affected
WITH t(json_extracted,str) AS
(
SELECT JSON_VALUE (JSONDetail, '$.comment'),
SUBSTRING(
JSONDetail,
PATINDEX('%"comment"%', JSONDetail),
PATINDEX('%"contacts"%', JSONDetail)-PATINDEX('%"comment"%', JSONDetail)
)
FROM tab
), t2(json_extracted,str) AS
(
SELECT json_extracted,
TRIM(
SUBSTRING( str, PATINDEX('%:%', str) + 1,
PATINDEX('%,%', str) - PATINDEX('%:%', str) - 1 ) )
FROM t
)
SELECT SUBSTRING(str,2,LEN(str)-2) AS extracted_comment,
CASE WHEN json_extracted = SUBSTRING(str,2,LEN(str)-2)
THEN
'No'
ELSE
'Yes'
END AS "is_it_corrupted"
FROM t2
| extracted_comment | is_it_corrupted |
|---|---|
| test "word" test | Yes |
| test word test | No |