Using to_base64 function and regexp_replace with backreferences in MariaDB 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)]&gt; 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)]&gt; 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)]&gt; 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)]&gt; 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?
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:


Find first pattern occurency offset in source data starting from some offset (initially 1)
Split source by head, pattern and tail
Perform pattern replace and encode
Concat head and encoded replace, calculate its length (it will be used as search starting offset on the next iteration)
Concat tail
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.