I would like to remove the first part of the separated string and the comma. However, I cannot simply use a query that removes the first three characters of a string as the character lengths vary (i.e. 10th).
Original data:
1st,4000;2nd,1200;3rd,600;4th,200;total_value,6000;
Code to separate strings:
SELECT
SUBSTRING_INDEX((SUBSTRING_INDEX(PrizeMoneyBreakDown,';',1)),';',-1) AS 1st,
SUBSTRING_INDEX((SUBSTRING_INDEX(PrizeMoneyBreakDown,';',2)),';',-1) AS 2nd
FROM race2
Output of code (separated strings):
1st,4000
2nd,1200
Desired output of code:
4000
1200
CodePudding user response:
If you're running MySQL 8 , then REGEXP_SUBSTR
works here:
SELECT
REGEXP_SUBSTR(PrizeMoneyBreakDown, '[0-9] ', 1, 2) AS first,
REGEXP_SUBSTR(PrizeMoneyBreakDown, '[0-9] ', 1, 4) AS second
FROM race2;