Removing certain characters in all rows that match a regex?

Question

I'd like to change

{foo, {bar}, foobar}

to

{foo, bar, foobar}

in all rows that match '{.*{'. I.e. remove all curly braces { and } except the outer most pair.

So doing

mysql -h $H -u $U -p$P $DB -B -e "SELECT id FROM t WHERE col REGEXP '{.*{'" > bad.txt

selects all the rows that will need this substitution. How do I make this substitution very quickly?

EDIT:

Could I do it by

update table set column = REPLACE(column,'{','');

Then restore the out most pair

update table set column = REPLACE(column,'^','{');

update table set column = REPLACE(column,'$','}');

I get error

mysql> EXPLAIN UPDATE t SET col=REPLACE(REPLACE(REPLACE(REPLACE(col,'{{','{'),'}}','}'), ', {', ', '), '}, ', ', ');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE t SET col=REPLACE(REPLACE(REPLACE(REPLACE(' at line 1

There is no regexp-replace in mysql (though you could use a custom function to handle doing it with replace()). Duplicate of http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Bob Davies, Jun 30 '12 at 14:18

score 2 · Answer 1 · answered Jun 30 '12 at 14:24

2

I'm sure there's a more detailed method to do this in one step, but the quick 'n dirty way would be to remove all {}'s and just wrap the entire set once you're done.

UPDATE table
SET column = CONCAT('{', REPLACE(REPLACE(column,'{',''),'}',''), '}')
WHERE column REGEXP '{.*{'

answered Jun 30 '12 at 14:24

Kevin McKelvin

3,467
1
27
27

This may have the side-effect of destroying (assuming this is json data that's not fully quoted above) child-objects like { foo, bar: { a, b }, foobar } – Bob Davies Jun 30 '12 at 14:38
I need to change `{ foo, bar: { a, b }, foobar }` to `{ foo, bar: a, b, foobar }` in that case. – qazwsx Jun 30 '12 at 14:42
If you run that in MySQL Workbench, I get: `SELECT CONCAT('{', REPLACE(REPLACE('{ foo, bar: { a, b }, foobar }','{',''),'}',''), '}')` = `'{ foo, bar: a, b , foobar }'` – Kevin McKelvin Jun 30 '12 at 14:46
It won't destroy elements in child objects, but it will leave an extra whitespace around it. If you need the whitespace to be correct as well, you can clear it by adding further replace commands taking the `'{ '` and `' }'` into account. No data will be destroyed though. – Kevin McKelvin Jun 30 '12 at 14:49

score 0 · Accepted Answer · answered Jun 30 '12 at 15:11

Unfortunately MySQL doesn't support back references in regular expressions(allow grouping subexpressions with parentheses and recalling the value they match in the same expression). you can export the result of your SELECT query into a text file (e.g. in CSV format), replace all occurrence of

{(.*){(.*)}(.*)}

with:

\1\2\3

with a text editor which supports the Perl regular expression syntax, and then replace the original rows with modified ones.

notice that this query:

UPDATE table
SET column = CONCAT('{', REPLACE(REPLACE(column,'{',''),'}',''), '}')
WHERE column REGEXP '{.*{'

will work only if the entire text is guaranteed to be wrapped by a pair of braces!

Removing certain characters in all rows that match a regex?

EDIT:

I get error

2 Answers2