Search code examples
mysqlregexregexp-replace

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?

Solution

  • 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.