Exracting substring from given string

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.

---

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.

---

With SUBSTR:

with strings as (
  select 'MAXO_INSTR_INTERFACE' as string from dual
  union all
  select 'MAXIS_VENDOR_INTERFACE' from dual
  union all
  select 'MAXIMOS_EMPS_INTERFACE2' from dual
)
select substr(string,
              instr(string, '_', 1, 1) + 1,
              instr(string, '_', 1, 2) - instr(string, '_', 1, 1) - 1
              ) as substr from strings;

Returns:

SUBSTR
---------------------------------------------------------------------
INSTR
VENDOR
EMPS

But a regular expression solution is easier to understand.

The question has also a PL/SQL tag:

create or replace function f (p_str in varchar2) return varchar2 as
  v_begin constant pls_integer := instr(p_str, '_', 1, 1) + 1;
  v_len constant pls_integer := instr(p_str, '_', 1, 2) - v_begin;
begin
  return substr(p_str, v_begin, v_len);
end;

Returns:

begin
  dbms_output.put_line(f('MAXO_INSTR_INTERFACE'));
  dbms_output.put_line(f('MAXIS_VENDOR_INTERFACE'));
  dbms_output.put_line(f('MAXIMOS_EMPS_INTERFACE2'));
end;
/

INSTR
VENDOR
EMPS

PL/SQL procedure successfully completed.

---

This will return the string between the underscores:

WITH t AS (SELECT 'MAXO_INSTR_INTERFACE' AS txt FROM DUAL
           UNION
           SELECT 'MAXIS_VENDOR_INTERFACE' AS txt FROM DUAL
           UNION
           SELECT 'MAXIMOS_EMPS_INTERFACE2' AS txt FROM DUAL) 
SELECT REGEXP_REPLACE( txt, '(^.*\_)([[:alnum:]]*)(\_.*$)', '\2' )
  FROM t;

Returns:

INSTR  
VENDOR
EMPS 

The regex REGEXP_REPLACE( txt, '(^.*\_)([[:alnum:]]*)(\_.*$)', '\2' ) looks for the first underscore, then any alphanumerics up to the next underscore, finally the rest of the input before then replacing everything with the second section it found (i.e. the section between the underscores).

If there are going to be spaces in the text between the underscores then use REGEXP_REPLACE( txt, '(^.*\_)(([[:alnum:]]|[[:space:]])*)(\_.*$)', '\2' ), for a full lowdown of the REGEXP matching possibilities there is a good article here:

http://orafaq.com/node/2404

Hope it helps...