你不需要去掉空格。您可以找到后跟零或多个空格的数字，然后是h或m表示小时和分钟。

SELECT duration,
       COALESCE(TO_NUMBER(REGEXP_SUBSTR(duration, '(\d+)\s*h', 1, 1, 'i', 1)), 0) * 60
       + COALESCE(TO_NUMBER(REGEXP_SUBSTR(duration, '(\d+)\s*m', 1, 1, 'i', 1)), 0)
         AS minutes
FROM   table_name;

或十进制小时和分钟：

SELECT duration,
       COALESCE(TO_NUMBER(REGEXP_SUBSTR(duration, '(\d+\.?\d*|\.\d+)\s*h', 1, 1, 'i', 1)), 0) * 60
       + COALESCE(TO_NUMBER(REGEXP_SUBSTR(duration, '(\d+\.?\d*|\.\d+)\s*m', 1, 1, 'i', 1)), 0)
         AS minutes
FROM   table_name;

其中，对于样本数据：

CREATE TABLE table_name (duration) AS
SELECT '2hr' FROM DUAL UNION ALL
SELECT '2hr 30min' FROM DUAL UNION ALL
SELECT '3 hours' FROM DUAL UNION ALL
SELECT '3 hr' FROM DUAL UNION ALL
SELECT '3 hrs' FROM DUAL UNION ALL
SELECT '3hr' FROM DUAL UNION ALL
SELECT '3hrs' FROM DUAL UNION ALL
SELECT '4 hours' FROM DUAL UNION ALL
SELECT '4 hrs' FROM DUAL UNION ALL
SELECT '4 hrs 10 mins' FROM DUAL UNION ALL
SELECT '6 hrs' FROM DUAL UNION ALL
SELECT '1.5 hrs' FROM DUAL UNION ALL
SELECT '.25 hrs' FROM DUAL UNION ALL
SELECT '1.1m' FROM DUAL;

输出：
| 持续时间|分钟|
| --|--|
| 2hr| 120 |
| 2小时30分钟| 150 |
| 3小时| 180 |
| 3小时| 180 |
| 3小时| 180 |
| 3hr| 180 |
| 3小时| 180 |
| 4小时| 240 |
| 4小时| 240 |
| 4小时10分钟| 250 |
| 6小时| 360 |
| 1.5小时| 90 |
| 0.25小时| 15 |
| 1.1m |1.1|
fiddle