Using REGEXP_SUBSTR as split in Oracle

Question

I have A.FIELD which outputs the following format:

9:30AM - 12:50PM AbCdEfGhIj XY-000

I am trying to figure out a way to get each individual value based on a specified delimiter.

1. All ints before 1st : (ex: 9)
2. All ints after 1st - or 2nd space, and before 2nd : (ex: 12)
3. All chars before 3rd space (ex: 9:30AM - 12:50PM)
4. All chars before the last space and after 2nd to last space (ex: AbCdEfGhIj)

Right now I'm able to get the following:

REGEXP_SUBSTR(A.FIELD, '\S*$')                        -- XY-000
REGEXP_SUBSTR(A.UP_CLASS_MEETINGS, '[^ ]+', 1)        -- 9:30AM

Is my second example to get 9:30AM the best method? Is it flawed?

How can I best utilize SUBSTR, INSTR, and/or REGEXP_SUBSTR?

Solution

regexp_substr(a.field, '(\d+):', 1, 1, '', 1),
regexp_substr(a.field, '(\d+):', 1, 2, '', 1),
regexp_substr(a.field, '\S+(\s|-)+\S+'),
regexp_substr(a.field, '\S+(\s|-)+\S+\s+(.*)\s+\S+$', 1, 1, '', 2)

fiddle

Comments

[O P]

Thanks, works great with the exception of the last line. I've edit my post.

[Jenna Leaf]

I apply the 4th one: SELECT REGEXP_SUBSTR ('Tag: XXXX (TEM4B19318D ) Type: TRIP ONLY Removed from: POINT S_BAY CAPBNK 7F94 SW', '\sCAPBNK\s(\w+)\s', 1, 1, '', 1) FROM dual; it works like charm with result 7F94 . Yet, I don't know why it works. Somebody please explains to me.