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

i need run regex find-and-replace against column named message in mysql table named post.

my database running mariadb 10.

according docs, mariadb 10 has new regexp_replace function designed this, can't seem figure out actual syntax.

it affect 280,000 rows, ideally there's way limit changing 1 specific row @ time while i'm testing it, or doing select rather update until i'm sure want.

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

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

the following tried, throws sql error:

update post set message = regexp_replace(message, '\[quote\sauthor=(.+)\slink=[^\]]+]', '[quote="$1"]') post_id = 12

in case, original message was: [quote author=jon_doe link=board=2;threadid=125;start=40#msg1206 date=1065088] , end result should [quote="jon_doe"]

what proper syntax make regexp_replace work?

you have lot of escaping here:

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

please note have reference group \\1