MySQL – Replace string when it appears

March 2, 2022

The column ‘PrizeMoneyBreakDown’ includes a number of strings seperated by a semi-colon. I am trying to remove the strings ‘total value’, ‘trophy total value’, and ‘welfare fund’ from the data. These strings only appear sometimes in the data so it is not as simple as just removing the last three strings. I need to write a query that removes the strings IF they appear.

Example of 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 – does not remove prize money associated with string):

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

>Solution :

On MySQL 8+, we can use REGEXP_REPLACE:

SELECT PrizeMoneyBreakDown,
       REGEXP_REPLACE(PrizeMoneyBreakDown,
                      '(total_value|welfare_fund|trophy_total_value),\\d+;',
                      '') AS NewPrizeMoneyBreakDown
FROM race2;

If you want to update the actual column then use:

UPDATE race2
SET PrizeMoneyBreakDown = REGEXP_REPLACE(
    PrizeMoneyBreakDown,
    '(total_value|welfare_fund|trophy_total_value),\\d+;',
    '')
WHERE PrizeMoneyBreakDown REGEXP '(total_value|welfare_fund|trophy_total_value),\\d+;';