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?
to_base64('\\2\\1')producing'XDJcMQ=='. Then it executesregexp_replace('abc', '(a)(b)', 'XDJcMQ==')producingXDJcMQ==c.