'MySQL - Replace string if it appears

I am trying to remove 'total value', 'trophy total value', and 'welfare fund' from the data.

MySQL 8.

Data:

1st,5285;2nd,1680;3rd,885;4th,550;5th,350;6th,350;7th,350;8th,350;total_value,10000;welfare_fund,200;trophy_total_value,150;

Desired output of data:

1st,5285;2nd,1680;3rd,885;4th,550;5th,350;6th,350;7th,350;8th,350

Current code (only removes the words 'total value' etc):

SELECT PrizeMoneyBreakDown,
  REPLACE(REPLACE(REPLACE(PrizeMoneyBreakDown,'total_value',""),'welfare_fund',""),'trophy_total_value',"") as new
FROM race2;
mysqlsqlworkbench


Solution 1:[1]

With REGEXP_REPLACE you could do something like:

SELECT regexp_replace('1st,5285;2nd,1680;3rd,885;4th,550;5th,350;6th,350;7th,350;8th,350;total_value,10000;welfare_fund,200;trophy_total_value,150;', '(total\_value|welfare\_fund|trophy\_total\_value)[^;]*;', '')

IF you are on an older version of mysql, though, this function may not be supported.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JNevill

Related Questions