What is the correct syntax for a Regex find-and-replace using REGEXP_REPLACE in MariaDB?

Question

I need to run a regex find-and-replace against a column named message in a MySQL table named post.

My database is running MariaDB 10.

According to the docs, MariaDB 10 has a new REGEXP_REPLACE function designed to do exactly this, but I can't seem to figure out the actual syntax.

It will affect 280,000 rows, so ideally there's also a way to limit it to only changing one specific row at a time while I'm testing it, or simply doing a SELECT rather than an UPDATE until I'm sure it does what I want.

The regex I want to run: \[quote\sauthor=(.+)\slink=[^\]]+]

The replacement string: [quote="$1"]

The following was what I tried, but it just throws a SQL error:

UPDATE post SET message = REGEXP_REPLACE(message, '\[quote\sauthor=(.+)\slink=[^\]]+]', '[quote="$1"]') WHERE post_id = 12

In this case, the original message was: [quote author=Jon_doe link=board=2;threadid=125;start=40#msg1206 date=1065088] and the end result should be [quote="Jon_doe"]

What is the proper syntax to make this REGEXP_REPLACE work?

Answer 1

You have to do a lot of escaping here:

REGEXP_REPLACE(message, "\\[quote\\sauthor=(.+)\\slink=[^\\]]+]", "\\[quote=\"\\1\"\\]")

Please note that you have to reference the Group by \\1