Search code examples
mysqlregexp-replace

mysql regexp_replace with update

I just upgraded MySQL to 8.0.11 to be able to use the regexp_replace. It works fine in a select statement, but when I use the same in an update, I get strange results. Here is a simplified example: if I have a field with "567890", and I updated it with this:

update test set field = regexp_replace(field, '[7]', 'z')

instead of "56z890", the field value is set to "56".

This must be a bug, but in the meantime, are there any workarounds to get it to work as expected? Thanks.

Solution

  • It looks like a bug of the REGEXP_REPLACE function. In MariaDB it works as expected, see dbfiddle.

    I'll try to report the bug in bugs.mysql.com. Bug was already reported, Bug #90803 regexp_replace accumulating result and Bug #90870 REGEXP_REPLACE truncate UPDATE.

    A workaround is:

    UPDATE `test`
SET `field` = CAST(REGEXP_REPLACE(`field`, '[7]', 'z') AS CHAR);

    See dbfiddle.