Richard Ellerbrock via discuss <discuss@lists.mariadb.org> writes:

> I am trying to understand the behaviour of REGEXP_REPLACE when there is a
> backslash in the subject string. It appears that the subject needs to have
> \'s escaped which is not desirable (see example 1 below). I can understand

This is the behaviour of string litterals in mariadb (quoted string
constants), it is not related to REGEXP_REPLACE. From the documentation:

  https://mariadb.com/kb/en/string-literals/

  "Backslash (\), if not used as an escape character, must always be
   escaped. When followed by a character that is not in the above table,
   backslashes will simply be ignored."

But it's possible to set NO_BACKSLASH_ESCAPES in the @@sql_mode, then
backslash needs not be escaped:

  MariaDB [knielsen]> select 'A\BC';
  | ABC |

  MariaDB [knielsen]> select 'A\\BC';
  | A\BC |

  MariaDB [knielsen]> set session sql_mode=no_backslash_escapes;
  MariaDB [knielsen]> select 'A\BC';
  | A\BC |

  MariaDB [knielsen]> select 'A\\BC';
  | A\\BC |

Because of the need to escape backslash in the string litterals, you need
double backslash (when no_backslash_escapes is not set) to escape specials
in the regexp:

  MariaDB [knielsen]> select regexp_replace('a.b', '\.', '!');
  | !!!                              |

  MariaDB [knielsen]> select regexp_replace('a.b', '\\.', '!');
  | a!b                               |

Remeber that this is the behaviour of the SQL parser when it parses a string
litteral. If the subject or regexp in REGEXP_REPLACE comes from a table
column or prepared-statement placeholder, or anywhere else the value is not
parsed as a string litteral, the backslash should not be escaped.

 - Kristian.