CODEWITHSUNDEEP
mysqlregexp-replace
Ray Gurganus
Ray Gurganus

Reputation: 33

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.

Upvotes: 3

Views: 13450

Answers (2)

Silambarasan R
Silambarasan R

Reputation: 1556

I'm using Apache Version(PHP) 5.6 and MySQL version 10.1.25-Maria DB Localhost Server.

I've tried with the following query,

UPDATE `table_name` 
SET         
    column_name = REGEXP_REPLACE(column_name,regex_pattern_to_find,replaceable_text)
WHERE
    column_name RLIKE 'regex_pattern_to_find'

It was working fine for me. Hope this helps.

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

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.

Upvotes: 4

Related Questions