0

I have a table with a column details like -

1                   IPFGH Cycle 2 Work Schedule  7.78 hrs  - 30 min lunch - 07:15:00 AM to 03:32:00 PM Work Time
2                   IPFGH Cycle 1 Work Schedule  7.78 hrs  - 60 min lunch - 08:00:00 AM to 04:47:00 PM Work Time
3                   IPFGH Cycle 1 Work Schedule  7.78 hrs  - 30 min lunch - 07:47:00 AM to 04:04:00 PM Work Time
4                   IPFGH Cycle 2 Work Schedule  7.78 hrs  - 60 min lunch - 06:45:00 AM to 03:32:00 PM Work Time
5                   IPFGH Part Time WTF Cycle Work Schedule No EDO 7 hrs  - 60 min lunch - 08:00:00 AM to 04:00:00 PM Work Time

I want to extract number with hrs mentioned. so the output should be -

Updated Output

1                   7.78
2                   7.78
3                   7.78
4                   7.78
5                   7

Which function to use for this ?

I am using the below query but its returning null

regexp_substr (regexp_substr(SCHEDULE_NAME, '([[:alpha:]]+)[[:space:]]hrs', 1), '^[^ ]+', 1)
SSA_Tech124
  • 577
  • 1
  • 9
  • 25

1 Answers1

3

You can use REGEXP_SUBSTR:

SELECT id,
       TO_NUMBER(REGEXP_SUBSTR(schedule_name, '(\d+\.?\d*)\s+hrs', 1, 1, 'i', 1)) AS hrs
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (id, schedule_name) AS
SELECT 1, 'IPFGH Cycle 2 Work Schedule  7.78 hrs  - 30 min lunch - 07:15:00 AM to 03:32:00 PM Work Time' FROM DUAL UNION ALL
SELECT 2, 'IPFGH Cycle 1 Work Schedule  7.78 hrs  - 60 min lunch - 08:00:00 AM to 04:47:00 PM Work Time' FROM DUAL UNION ALL
SELECT 3, 'IPFGH Cycle 1 Work Schedule  7.78 hrs  - 30 min lunch - 07:47:00 AM to 04:04:00 PM Work Time' FROM DUAL UNION ALL
SELECT 4, 'IPFGH Cycle 2 Work Schedule  7.78 hrs  - 60 min lunch - 06:45:00 AM to 03:32:00 PM Work Time' FROM DUAL UNION ALL
SELECT 5, 'IPFGH Part Time WTF Cycle Work Schedule No EDO 7 hrs  - 60 min lunch - 08:00:00 AM to 04:00:00 PM Work Time' FROM DUAL;

Outputs:

ID HRS
1 7.78
2 7.78
3 7.78
4 7.78
5 7

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117