regexmariadbregexp-replacemariadb-10.3

Regular expression replace in MariaDB

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

Solution

  • 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.