select and replace data using regexp in mySql

Question

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?

your query works for me, unless the problem is in he subquery (which you didn't show): db-fiddle.com/f/jmJ8YsjV7PhM6DLdpCs7xe/0 — guido
– guido, Commented Nov 1, 2018 at 13:48
There is also a question mark over the table design: why _2, _3 etc.? Presumably, these suffixes signify something that should probably be identified in another column. — Andy G
– Andy G, Commented Nov 1, 2018 at 14:39
the subfixes depend on previous calculations that at the top level need to be summerized (lets say different discount policies etc). The subquery that gives the t table works properly and just makes some calculations based on names and gives this list on the left) — kyrpav
– kyrpav, Commented Nov 1, 2018 at 15:09
i also found that it does this after some row in general and not only when it finds _2 — kyrpav
– kyrpav, Commented Nov 1, 2018 at 15:53

Rick James · Accepted Answer · 2018-11-01 18:43:18Z

1

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.

answered Nov 1, 2018 at 18:43

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

5 Comments

kyrpav Over a year ago

this is fixed though i was using community 8.0.11. I have upgraded to 8.0.12 from the installer and it works ok

kyrpav Over a year ago

i have found that regexp_replace has problem with group by is there any workaround?

Rick James Over a year ago

@kyrpav - I have not heard of a workaround; what is the issue now that you have upgraded? (Or do I misunderstand you?) If it is a different query, please provide the specifics.

kyrpav Over a year ago

if i do not group by it works it replaces the names and deletes the _2,_3 but if i try to group by in order to aggregate sum numbers then it deletes all data from fruit column and gives an empty column

Rick James Over a year ago

@kyrpav - If you would like help with that, start a new Question, use 8.0.12, and focus on its problem.

Collectives™ on Stack Overflow

select and replace data using regexp in mySql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related