Exracting substring from given string

Question

I have data as following

1)MAXO_INSTR_INTERFACE    
2)MAXIS_VENDOR_INTERFACE
3)MAXIMOS_EMPS_INTERFACE2

I need to extract String which is located between both underscores in PL/SQL as

INPUT                    EXPECTED OUTPUT
------------------------ ---------------
MAXO_INSTR_INTERFACE     INSTR   
MAXIS_VENDOR_INTERFACE   VENDOR  
MAXIMOS_EMPS_INTERFACE2  EMPS

I have tried with substring function but i am unable to perform accurately.

Answer 1

A slightly easier regular expression:

SQL> with t as
  2  ( select 'maxo_instr_interface' as txt from dual union all
  3    select 'maxis_vendor_interface' from dual union all
  4    select 'maximos_emps_interface2' from dual
  5  )
  6  select txt
  7       , regexp_substr(txt,'[^_]+',1,2)
  8    from t
  9  /

TXT                     REGEXP_SUBSTR(TXT,'[^_]
----------------------- -----------------------
maxo_instr_interface    instr
maxis_vendor_interface  vendor
maximos_emps_interface2 emps

3 rows selected.

Regards,
Rob.