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.

All ints before 1st : (ex: 9)
All ints after 1st - or 2nd space, and before 2nd : (ex: 12)
All chars before 3rd space (ex: 9:30AM - 12:50PM)
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?

Egor Skriptunoff · Accepted Answer · 2013-06-04 17:21:16Z

5

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

edited Jun 4, 2013 at 17:21

answered Jun 4, 2013 at 16:34

Egor Skriptunoff

23.8k2 gold badges37 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

O P Over a year ago

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

Jenna Leaf Over a year ago

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.

Collectives™ on Stack Overflow

Using REGEXP_SUBSTR as split in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related