I have table in which one of the column(info) contains below type of information, I want to run a query which check all the records and if it finds ""matched data"" value then remove the data.
Current value
"{""value"":{""Volume ID"":""12345"",""matched data"":""eJzBFPafhF1xu0JdwH""}}"
Expected value
"{""value"":{""Volume ID"":""12345"",""matched data"":"" ""}}"
If your Json format string is fixed. you can use CHARINDEX with SUBSTRING
and REPLACE method
""matched data"" string to be start index by CHARINDEX methodCHARINDEX to get }} to get the be the end index.SUBSTRING get contain ""matched data"" key value data.REPLACE to your expect format.Final use Replace to get your expected result
TestDLL
CREATE TABLE T(
Col1 VARCHAR(1000)
);
INSERT INTO T VALUES ('"{""value"":{""Volume ID"":""12345"",""matched data"":""eJzBFPafhF1xu0JdwH""}}"');
INSERT INTO T VALUES ('"{""value"":{""Volume ID"":""12345"",""matched data"":""test1213""}}"');
Query
SELECT REPLACE(Col1,SUBSTRING(Col1,CHARINDEX('""matched data"":', Col1) ,CHARINDEX('}}', Col1) - CHARINDEX('""matched data"":', Col1)),'""matched data"":"" ""')
FROM T
idk what exactly do you need, but if you want to find out the solution to the if statement in sql your way is to use case.
Example:
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END
Example on a full query:
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
JSON?