Richard Ellerbrock via discuss
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.