MySQL: How can I convert a string like "1 h 15 min" into 75?

mysql

Solution

Short hack:

SET @ugly_time = '1h 2min';
SELECT TIME_TO_SEC(
    COALESCE(
        STR_TO_DATE(@ugly_time, '%Hh %imin'),
        STR_TO_DATE(@ugly_time, '%imin')
    )
) AS seconds;

(works only for times < 24h)

Problem

Is it posssible to convert a string like `"1 h 15 min"` into `75` with a SQL only solution? Edit: The string may also be in the format `"1 h"` or `"15 min"` in some cases, but it never contains days and seconds.

Original source