CODEWITHSUNDEEP
sqlregexoracle-databaseplsqlsubstr
O P
O P

Reputation: 2365

Using REGEXP_SUBSTR as split in Oracle

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?

Upvotes: 2

Views: 32773

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

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

Upvotes: 5

Related Questions