How to replace capitals in sub-data fields?

JatS posted 4 months ago in General

In our MySql database the data is stored in combined json-strings. The table looks like this:

| ID | DATA |

| 100 | {Var1Str: "sometxt", Var2Double: 0,01, Var3Integer: 1} |

| 101 | {Var3Integer: 5, Var2Double: 2,05, Var1Str: "txt"} |

The result needs to look like this (same (sub)variable names but all lower letters):

| ID | DATA |

| 100 | {var1str: "sometxt", var2double: 0,01, var3integer: 1, var4str: "another text"} |

| 101 | {var3integer: 5, var2double: 2,05, var1str: "txt", var4str: "more text"} |

Please help!

JatS posted 4 months ago

I see this is possible with the search and replace function (CTRL + R), where case sensitivity can also be selected. However I need to do this for about 30 variables on a daily basis.

JatS posted 4 months ago

Found it!

This will solve the problem:

UPDATE table SET table.DATA = REPLACE(DATA,'Var1Str":','var1str":') WHERE id_client = 12; UPDATE table SET table.DATA = REPLACE(DATA,'Var2Double":','var2double":') WHERE id_client = 12; UPDATE table SET table.DATA = REPLACE(DATA,'Var3Integer":','var3integer":') WHERE id_client = 12;

JatS posted 4 months ago

Sorry for bad formatting, here is the same in proper format:

UPDATE table SET table.DATA = REPLACE(DATA,'Var1Str":','var1str":') WHERE id_client = 12;
UPDATE table SET table.DATA = REPLACE(DATA,'Var2Double":','var2double":') WHERE id_client = 12;
UPDATE table SET table.DATA = REPLACE(DATA,'Var3Integer":','var3integer":') WHERE id_client = 12;

Please login to leave a reply, or register at first.