我想取'bbb_0700' 、'A2005_X_abc' 的第一个下划线后第二个下划线前的字符,如果没有第二个下划线就是取第一个下划线后的字符
select replace(REGEXP_SUBSTR('bbb_0700', '(_).+?(_)+?'), '_', '') from dual;--不好使
select replace(REGEXP_SUBSTR('A2005_X_abc', '(_).+?(_)+?'), '_', '') from dual;--好使
select replace(REGEXP_SUBSTR('bbb_0700', '(_).+?(_)+?'), '_', '') from dual;--不好使
select replace(REGEXP_SUBSTR('A2005_X_abc', '(_).+?(_)+?'), '_', '') from dual;--好使
--try it
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
Connected as topeng
SQL>
SQL> select decode(occ,
2 0,
3 regexp_replace(name, '(.*)_(.*)', '\2'),
4 regexp_replace(name, '(.*)_(.*)_(.*)', '\3'))
5 from (select 'bbb_0700' name, instr('bbb_0700', '_', 1, 2) occ from dual)
6 /DECODE(OCC,0,REGEXP_REPLACE(NA
------------------------------
0700SQL>
SQL> select decode(occ,
2 0,
3 regexp_replace(name, '(.*)_(.*)', '\2'),
4 regexp_replace(name, '(.*)_(.*)_(.*)', '\3'))
5 from (select 'A2005_X_abc' name, instr('A2005_X_abc', '_', 1, 2) occ from dual)
6 /DECODE(OCC,0,REGEXP_REPLACE(NA
------------------------------
abcSQL>