Extract value from a string in oracle

Question

I need to extract the certain values from the string.

Example: 1571-P003 031-OHD-SSKS-SSMO

Output : P003 031

Answer 1

Here's one option, which returns the 2nd and the 3rd word from that string:

SQL> with test (col) as
  2    (select '1571-P003 031-OHD-SSKS-SSMO' from dual)
  3  select regexp_substr(col, '\w+', 1, 2) ||' ' ||
  4         regexp_substr(col, '\w+', 1, 3) result
  5  from test;

RESULT
--------
P003 031

SQL>

Or, another, which returns substring between the 1st and the 2nd - sign:

SQL> with test (col) as
  2    (select '1571-P003 031-OHD-SSKS-SSMO' from dual)
  3  select substr(col, instr(col, '-', 1, 1) + 1,
  4                     instr(col, '-', 1, 2) - instr(col, '-', 1, 1) - 1
  5               ) result
  6  from test;

RESULT
--------
P003 031

SQL>