Regular expression replace in MariaDB

Question

I am trying to match internal links in an SMF forum and convert the BBC tag from url to iurl so that a link like this:

[url=https://www.translatum.gr/forum/index.php?topic=989144.0]κατ' ἐπιταγήν -> by way of command[/url]

Will become:

[iurl=https://www.translatum.gr/forum/index.php?topic=989144.0]κατ' ἐπιταγήν -> by way of command[/iurl]

I have tried something like this on PhpMyAdmin (using MariaDB 10.3)

UPDATE smf_messages
SET body = REGEXP_REPLACE(body, 
    '(\[url=https:\/\/www\.translatum\.gr)(.*?)(\[\/url\])', 
    '[iurl=https://www.translatum.gr\\2[/iurl]
  ')
WHERE ID_TOPIC = 987873

which although appears to work on a test in regex101 gives garbage output on SQL (i.e. it is replaced with multiple iterations of the link and its text). I have used this for syntax reference

Wiktor Stribiżew · Accepted Answer · 2020-12-24 16:21:50Z

2

You need to double escape the backslash in the string literal to actually define the literal backslash char that forms regex escapes.

Also, your regex replacement is too redundant, you may capture more text and make your replacement even shorter:

REGEXP_REPLACE(body, '\\[(url=https://www\\.translatum\\.gr.*?)\\[/url]', '[i\\1[/iurl]')

See this regex demo.

answered Dec 24, 2020 at 16:21

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Comment

greektranslator Over a year ago

Wow, amazing, multiple escapes get me confused :) Pity that regex101 does not have a MySQL flavor.

Collectives™ on Stack Overflow

Regular expression replace in MariaDB

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related