好方法取AAA_XXXXXXX_CDEF_BBBBB 第一个"_"和第二个"_"之间的串？

Question

如题

Answer 1

SQL> with temp as
  2  (select 'AAA_XXXXXXX_CDEF_BBBBB' text from dual)
  3  select substr(text, instr(text, '_')+1, instr(text, '_', 1, 2)-instr(text, '_')-1)  from temp;SUBSTR(
-------
XXXXXXX

Answer 2

select  substr('AAA_XXXXXXX_CDEF_BBBBB',instr('AAA_XXXXXXX_CDEF_BBBBB','_',1,1),
instr('AAA_XXXXXXX_CDEF_BBBBB','_',1,2)-instr('AAA_XXXXXXX_CDEF_BBBBB','_',1,1)) from dual

Answer 3

SQL> select REGEXP_SUBSTR('AAA_XXXXXXX_CDEF_BBBBB','(_).+?(_)') from dual;REGEXP_SU
---------
_XXXXXXX_SQL>

Answer 4

select replace(REGEXP_SUBSTR('AAA_XXXXXXX_CDEF_BBBBB','(_).+?(_)'),'_','') from dual