Oracle - Remove specific space from string

Question

we have some unformatted data which has been passed to us and need to convert it to minutes:

2hr
2hr 30min
3 hours
3 hr
3 hrs
3hr
3hrs
4 hours
4 hrs
4 hrs 10 mins
6 hrs

What I want to do is remove the first space from string, which will result in:

2hr
2hr 30min
3hours
3hr
3hrs
3hr
3hrs
4hours
4hrs
4hrs 10 mins
6hrs

Then, I can take the first "word" and convert it to minutes by removing everything not numeric and multiplying it by 60. After, I can take every after the first word and add it to that result. But I need to remove just the first space.

Am thinking REGEXP_REPLACE, but not sure how to work with only the first space.

Many thanks!

Answer 1

You do not need to get rid of the spaces. You can find digits that are followed by zero-or-more spaces and then either h or m for hours and minutes.

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;

or for decimal hours and minutes:

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;

Which, for the sample data:

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;

Outputs:

DURATION	MINUTES
2hr	120
2hr 30min	150
3 hours	180
3 hr	180
3 hrs	180
3hr	180
3hrs	180
4 hours	240
4 hrs	240
4 hrs 10 mins	250
6 hrs	360
1.5 hrs	90
.25 hrs	15
1.1m	1.1

fiddle