Replace a specific string value-CodePudding

Lets say I have this table

 ------- -------------------------------------------------------------------------- 
| id    | json_text                                                                |
 ------- -------------------------------------------------------------------------- 
| aWoJl | {"color":"#1B3451","help_text":"color is here but dont replace me"}      |
 ------- -------------------------------------------------------------------------- 
| r5Njc | {"color":"#1B3451","help_text":"color is also here but dont replace me"} |
 ------- --------------------------------------------------------------------------

Now I want to change color value from #1B3451 to #1e72d2. How can I do that?

I have tried using replace(), but no result as of now and when trying to use wildcards, it is throwing error.

Here is the SQL fiddle . http://sqlfiddle.com/#!18/6db3f9/1

CodePudding user response：

You have to convert first to VARCHAR or NVARCHAR then REPLACE

UPDATE temptable set [json_text] = REPLACE(CAST([json_text] AS NVARCHAR(MAX)),N'#1B3451',N'#1e72d2');