Sorting varchar fields for mysql database

Question

Trying to sort the following TEAM_TOTAL Column Desc

MATCHID     TEAM_TOTAL        
 ----------  ----------------- 
 573         Total   112       
 573         Total  2 for  115 
 574         Total  9 for  97  
 574         Total  2 for  100 
 575         Total  9 for  129 
 575         Total  9 for  101 
 576         Total  4 for  191 
 576         Total  9 for  160 
 577         Total  8 for  157 
 577         Total  7 for  137 
 578         Total  6 for  193 
 578         Total   119 

But the problem is TEAM_TOTAL is varchar, is there a way with query alone I can get the results in the sorted desc order. More over there is a text as well in that column. I am running out of ideas to get this up

Result should have beeen like the below Result Set

   MATCHID     TEAM_TOTAL        
  ----------  ----------------- 
  578         Total  6 for  193
  576         Total  4 for  191  
  576         Total  9 for  160 
  577         Total  8 for  157 
  577         Total  7 for  137
  575         Total  9 for  129
  578         Total   119 
  573         Total  2 for  115 
   573         Total   112 
  575         Total  9 for  101
  574         Total  2 for  100
  574         Total  9 for  97

Answer 1

Give this a try:

select * from t
order by substring(
    team_total, locate('for', team_total) + 
        if(locate('for', team_total) > 0, 4, 7))+0 desc

Fiddle here.

Answer 2

Try to extract the integer (string after the last space):

-- 'Total 112' - extracts 112
SELECT SUBSTRING('Total  112', LENGTH('Total  112') - LOCATE(' ', REVERSE('Total  112'))); 

-- 'Total  6 for  193' - extracts 193
SELECT SUBSTRING('Total  6 for  193', LENGTH('Total  6 for  193') - LOCATE(' ', REVERSE('Total  6 for  193')));

Now, you can convert that string to a number and then order by it.

SELECT * FROM teams
ORDER BY
CAST(SUBSTRING(TEAM_TOTAL, LENGTH(TEAM_TOTAL) - LOCATE(' ', REVERSE(TEAM_TOTAL))) AS INT) DESC