Regexp_substr to parse a string into pieces

Question

I have been stuck all morning on this and was hoping to get some help. I have been reading what I can find but I am having troubles applying it in my situation.

I have records similar to this:

A123-700
A123-700 / WORD-8
A123 / A456
WORD-8 / A456-800

I need to break these up into a "type" and "series" and ignore "WORD-8"

For example

A123-300 would be type=A123, series=300
A123-300 / WORD-8 would be type=A123, series=300
A123 / A456 would be type=A123, type=A456
WORD-8 / A456-200 would be type=A456, series=200

So far I have something like this:

WITH gen AS
  ( select 'A123-700' x from dual
  UNION ALL
  select 'A123-700 / WORD-8' x from dual
  union all
  select 'A123 / A456' x from dual
  union all
  select 'WORD-8 / A456-800' x from dual
  )
SELECT x ,
  regexp_substr(x, '[^/]+')              as first_slash,
  regexp_substr(x, '[^-]+')              as first_type,
  regexp_substr(x, '-\w*')               as first_series,
  regexp_substr(x, '[^/][^DASH]+', 1, 2) as second_slash,
  regexp_substr(x, '[^/]+', 1, 2)        as second_type,
  regexp_substr(x, '-\w+', 1, 2)         as second_series
FROM gen;

But the results are not what I was hoping for. I'd like to not have the -, and my "second" info is not coming out right either.

X                 FIRST_SLASH FIRST_TYPE  FIRST_SERIES  SECOND_SLASH  SECOND_TYPE SECOND_SERIES
A123-700          A123-700    A123        -700          (null)        (null)      (null)
A123-700 / WORD-8 A123-700    A123        -700          D-8           WORD-8      -8
A123 / A456       A123        A123 / A456 (null)        A456          A456        (null)
WORD-8 / A456-800 WORD-8      WORD        -8            D-8 /         A456-800    -800

Could someone help point me in the right direction?

Thanks!

Egor Skriptunoff · Accepted Answer · 2014-02-25 19:31:34Z

1

WITH 
  gen AS ( 
    select 'A123-700' x from dual
    UNION ALL
    select 'A123-700 / WORD-8' x from dual
    union all
    select 'A123 / A456' x from dual
    union all
    select 'WORD-8 / A456-800' x from dual
  ),
  t_slash as (
    SELECT x ,
      nullif(regexp_replace(x, '\s*/.*$'),'WORD-8') as first_slash,
      nullif(regexp_replace(x, '^[^/]*/?\s*'),'WORD-8') as second_slash
    FROM gen
  )
select x, first_slash, 
  regexp_substr(first_slash, '^[^-]*') as first_type,
  regexp_replace(first_slash, '^[^-]*-?') as first_series,
  second_slash,
  regexp_substr(second_slash, '^[^-]*') as second_type,
  regexp_replace(second_slash, '^[^-]*-?') as second_series
from t_slash

fiddle

answered Feb 25, 2014 at 19:31

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

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

2 Comments

froglander Over a year ago

That really helped, thanks! One question, what if it could be 'WORD-8' or 'WORD 8'?

Egor Skriptunoff Over a year ago

@froglander - You can use any string instead of WORD-8.

Maheswaran Ravisankar · Accepted Answer · 2014-02-25 19:38:24Z

1

WITH gen AS
  ( select 'A123-700' x from dual
  UNION ALL
  select 'A123-700 / WORD-8' x from dual
  union all
  select 'A123 / A456' x from dual
  union all
  select 'WORD-8 / A456-800' x from dual
  )
SELECT x ,
  regexp_substr(x,'A[[:alnum:]]+',1) as first_type,
  NULLIF( regexp_substr(x,'A[[:alnum:]]+',2),
          regexp_substr(x,'A[[:alnum:]]+',1))  as second_type,
  regexp_substr(x,'(A[[:alnum:]]+)-([[:digit:]]+)',1) as full,
  regexp_substr(regexp_substr(x,'(A[[:alnum:]]+)-([[:digit:]]+)',1),
                '-([[:digit:]]+)',
                1) as first_series
FROM gen;

edited Feb 25, 2014 at 19:38

answered Feb 25, 2014 at 19:32

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Collectives™ on Stack Overflow

Regexp_substr to parse a string into pieces

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related