mysql regexp_replace with update

Question

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.

maybe it's a stored function dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html — Deadooshka
– Deadooshka, Commented May 12, 2018 at 18:29

wchiquito · Accepted Answer · 2018-05-15 12:27:39Z

4

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.

edited May 15, 2018 at 12:27

answered May 12, 2018 at 21:03

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ray Gurganus Over a year ago

Yes, that workaround did fix it for a replace of one record. Then I tested a SELECT over a set of records, and the output is somehow adding the results of one record to the results of the next record. So by the end it is one concatenation of all the previous records. Doing it with an actual update could be disastrous, so I think I'll wait until the next revision comes out. Thanks again.

Ray Gurganus Over a year ago

Bug #90803 describes the second issue I found, in my previous comment. But this seems separate from my original issue, where a replace on a single row is truncating the result.

wchiquito Over a year ago

@RayGurganus: New bug reported, Bug #90870. See updated answer.

Silambarasan R · Accepted Answer · 2019-07-05 05:42:34Z

1

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.

answered Jul 5, 2019 at 5:42

Silambarasan R

1,60420 silver badges24 bronze badges

2 Comments

Heraknos Over a year ago

Why filter using where condition when regexp_replace already replace the targeted pattern ? Some kind of security ?

Silambarasan R Over a year ago

@Heraknos If you don't add the where condition, the query will check all the rows and update if the regex condition is matched. It'll consume more time when you've more rows, so we just have to filter the rows first and then replace the column further. Hope it helps ;)

Collectives™ on Stack Overflow

mysql regexp_replace with update

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related