CODEWITHSUNDEEP
mysqlregexregexp-replace
kyrpav
kyrpav

Reputation: 778

select and replace data using regexp in mySql

I am new to mySQL.I have a table with fruits (more than 10k rows). There are records of fruit names that are:

apples
apples_2
apples_3
...

I am trying to replace _2 or _3 ... under apples (to eliminate _\d) with regex in order to be able to sum another column that has budget.(was thinking that group by fruit will do the work)

I am using this code:

SELECT REGEXP_replace(t.fruit,'_\\d','') AS `fruitName` from Select(.....) t;

i have tried \d and \\d but it not correct and the problem is that when it finds the same data it answers like the next.

apples_2 -- applesapples 
apples_2 -- applesapplesapples 
apples_2 -- applesapplesapplesapples 
apples_2 -- applesapplesapplesapplesapples

What do i miss?

Upvotes: 0

Views: 89

Answers (1)

Rick James
Rick James

Reputation: 142518

That is a bug with REGEXP_REPLACE() in the particular version you are using. Please upgrade (or downgrade.)

The changelog for 8.0.12 says, among other things:

REGEXP_REPLACE() results from one result set row could carry forward to the next row, resulting in accumulation of previous results in the current row. (Bug #27992118, Bug #90803)

8.0.14 has been released.

Upvotes: 1

Related Questions