How to convert time given as string into integer mins

What I’m trying to ask is that I’ve a table Duration. It has only one field elapsed and it is VARCHAR. The table is this:

+-----------------+
|     elapsed     |
+-----------------+
| 30 min          |
| 30 min          |
| 1 hour          |
| 1 hour          |
| 1 hour 30 min   |
| 1 hour 30 min   |
+-----------------+

I want this table:

+------------------------+
| elapsed         |total |
+------------------------+
| 30 min          | 30   |
| 30 min          | 30   |
| 1 hour          | 60   |
| 1 hour          | 60   |
| 1 hour 30 min   | 90   |
| 1 hour 30 min   | 90   |
+-----------------+------+

total should be integer or number. I’ve to perform some arithmetic on it later.

Here’s what I tried:

case
WHEN substring_index(pa.Duration, 'hour', 1) > 0 and not substring_index(pa.Duration, 'min', 1) > 0
then cast(concat(pa.Duration,concat(' ' , 1)) as signed) * 60
WHEN substring_index(pa.Duration, 'hour', 1) > 0 and substring_index(pa.Duration, 'min', 1) > 0
then (cast(concat(pa.Duration,concat(' ' , 1)) as signed) * 60 + cast(concat(pa.Duration,concat(' ' , 3)) as signed) )
WHEN not substring_index(pa.Duration, 'hour', 1) > 0 and substring_index(pa.Duration, 'min', 1) > 0
then cast(concat(pa.Duration,concat(' ' , 1)) as signed)
else 0 end as minutes

Please help me.

Note: I need MYSQL query not MSSQL.

Answer

If you only have min and hour, you can use:

select elapsed, 
       ((case when elapsed like '% hour%'
              then substring_index(elapsed, ' hour', 1) * 60
              else 0
         end) +
        (case when elapsed like '%min%'
              then substring_index(substring_index(elapsed, ' min', 1), ' ', -1) + 0
              else 0
         end)
        ) as elapsed_minutes
from t;

This extracts the number before “hour” if any, multiplies by 60 and adds in the number before “minute”, if any.

Here is a db<>fiddle.