CODEWITHSUNDEEP
mysqlsqlworkbench
kjdcwckj
kjdcwckj

Reputation: 33

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;

Upvotes: 1

Views: 328

Answers (1)

JNevill
JNevill

Reputation: 50200

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.

Upvotes: 1

Related Questions