Search code examples
mysqlsqlworkbench

How to separate strings and then remove first part of seperated string in MySQL

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
Solution

  • 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;