取第一个段(A):
select RTRIM(REGEXP_SUBSTR('A_BC_DEF_GHI_JKL_MN','.+?(_)'),'_') from dual;
取第二个段(BC):
select replace(REGEXP_SUBSTR('A_BC_DEF_GHI_JKL_MN','(_).+?(_)'),'_','') from dual;
取第二个段(DEF):
???
……
select RTRIM(REGEXP_SUBSTR('A_BC_DEF_GHI_JKL_MN','.+?(_)'),'_') from dual;
取第二个段(BC):
select replace(REGEXP_SUBSTR('A_BC_DEF_GHI_JKL_MN','(_).+?(_)'),'_','') from dual;
取第二个段(DEF):
???
……
instr('A_BC_DEF_GHI_JKL_MN','_',2)代表 '_'第二次出现的位置
DEF 就在第二次和第三次之间select
substr('A_BC_DEF_GHI_JKL_MN',
instr('A_BC_DEF_GHI_JKL_MN','_',1,2)+1,
instr('A_BC_DEF_GHI_JKL_MN','_',1,3)-
(instr('A_BC_DEF_GHI_JKL_MN','_',1,2)+1)
)
from dual
--取第n个段,则用\n
SELECT REGEXP_REPLACE('A_BC_DEF_GHI_JKL_MN', '([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)','\3') FROM dual;
REG
---
DEF
--取第n个段,则用\nSELECT REGEXP_REPLACE('A_BC_DEF_GHI_JKL_MN', '([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)','\3')
FROM dual;
SELECT REGEXP_REPLACE('A_BC_DEF_GHI__MN', '([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)','\5')
FROM dual;
SELECT REGEXP_REPLACE('A_BC_DEF_GHI_ A_MN', '([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)_([[:alpha:]]+)','\5')
FROM dual;
Select REGEXP_REPLACE('A_BC_DEF_GH5I_ _MN','(.*)_(.*)_(.*)_(.*)_(.*)_(.*)', '\5') FROM dual;