Mysql update data with regex

Question

Is there anyway to update rows of one text column and remove extra spaces in between string,

The data is actually a JSON string, Following is the example string

{"ID"                                      :"95703",
 "LastName"         :"Trigger",
 "FirstName"                               :"Test", ... }

if you notice there are annoying white spaces between "ID" and its value :"95703",

is there anyway I can (in update query) update all rows and remove this white space (using regex)

the sample query I can run on this table is

UPDATE `user_log_data` SET data = {REGEX PART}

vks · Accepted Answer · 2014-11-19 09:33:23Z

1

"[ ]+:

Try this.Replace by ":.See demo.

answered Nov 19, 2014 at 9:33

vks

68.1k11 gold badges96 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

it will only search for the records right? can we remove those spaces in update query is the real question ?

@zzlalani you can use SUBSTR something like this to replace as well