6

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?

Improve this question

1 Answer 1

Reset to default
5

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

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

3 Comments

So in MySQL's flavor of regex an escape is '\\' rather than '\'? I was testing this regex in a few online tools with just the '\' and they were all treating that as valid
\ will escape something in SQL. But if you want to pass a \ to the REGEXP_REPLACE function you have to escape the escap-Symbol...
Where did name come from?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.