I am using MariaDB 10.3. I want to replace a string using REGEX and encode part of the result into base64.

For example, I want to first invert the "a" and "b" strings in this example:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', '\\2\\1');
| regexp_replace('abc', '(a)(b)', '\\2\\1') |
| bac                                       |
1 row in set (0.000 sec)

"abc" is now "bac, that's ok.

Now I want to base64-encode the result, so ba should become YmE=:

MariaDB [(none)]> SELECT to_base64('ba');
| to_base64('ba') |
| YmE=            |
1 row in set (0.000 sec)

But when I apply the to_base64 function within regexp_replace, it no longer interprets my backreferences and encodes \2\1 instead of the the ba string:

MariaDB [(none)]> SELECT regexp_replace('abc', '(a)(b)', to_base64('\\2\\1'));
| regexp_replace('abc', '(a)(b)', to_base64('\\2\\1')) |
| XDJcMQ==c                                            |
1 row in set (0.000 sec)

MariaDB [(none)]> select from_base64('XDJcMQ==');
| from_base64('XDJcMQ==') |
| \2\1                    |
1 row in set (0.000 sec)

How can we make the backreferences be interpreted BEFORE applying the to_base64 function?

  • 1
    Your code did ABSOLUTELY what you ask. Firstly it executes to_base64('\\2\\1') producing 'XDJcMQ=='. Then it executes regexp_replace('abc', '(a)(b)', 'XDJcMQ==') producing XDJcMQ==c.
    – Akina
    Commented Sep 13, 2019 at 9:43
  • How can we make the backreferences be interpreted BEFORE applying the to_base64 function? I see simple solution only for a case when the searching pattern occures in source only once. I can describe the algo how to perform this action in any case, but I don't want to build/debug the final code - it can become too complex.
    – Akina
    Commented Sep 13, 2019 at 9:44
  • Of course @Akina you're absolutely right, my bad! to_base64 is executed first obviously... so I needs another method to reach my goal. Thank you. Commented Sep 13, 2019 at 9:53

1 Answer 1


The algorythm as I see it.

We don't know how many replaces we need. So we must use recursive CTE or iterative/recursive code.

In it we must:

  1. Find first pattern occurency offset in source data starting from some offset (initially 1)
  2. Split source by head, pattern and tail
  3. Perform pattern replace and encode
  4. Concat head and encoded replace, calculate its length (it will be used as search starting offset on the next iteration)
  5. Concat tail
  6. Iterate next if current starting offset (last "first pattern occurency offset" - see step 1) > 0

In main query we select only those records where "first pattern occurency offset" = 0.

This algo can be realized:

  • using local variables @variable performing all chained calculations in excess fields which will be skipped in main query
  • in user-defined function which processes one literal value
  • in stored procedure

The process can be simplified if there is a guarantee that only one pattern match exists - but in that case the result for values which contains more occurences (randomly) will be unpredictable.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.