如果你想得到第二个和第三个的结果/
分隔组然后:
with mcte ( addr ) as (
select 'ILLD/ELKJS/00000000/ELKJS/FHSH' from dual union all
select 'ILLD/EFECTE/0116988-7-002/ADFA/ADFG' from dual union all
select 'IIODK/1573230-0/2216755-7/' from dual union all
select 'IIODK/1573230-0/2216755-700/WRITE' from dual union all
select 'IIODK/TEST/1573230-0/2216755-700/WRITE' from dual
)
select addr,
REGEXP_SUBSTR(addr,'^[^/]*/(\d+-\d)/',1,1,NULL,1) AS num1,
REGEXP_SUBSTR(addr,'^[^/]*/[^/]*/(\d+-\d)/',1,1,NULL,1) num2
from mcte;
Output:
ADDR NUM1 NUM2
-------------------------------------- ------------------- -------------------
ILLD/ELKJS/00000000/ELKJS/FHSH
ILLD/EFECTE/0116988-7-002/ADFA/ADFG
IIODK/1573230-0/2216755-7/ 1573230-0 2216755-7
IIODK/1573230-0/2216755-700/WRITE 1573230-0
IIODK/TEST/1573230-0/2216755-700/WRITE 1573230-0
Update:
如果您只想匹配第一个和第二个模式,而不关心它们在字符串中的位置,那么:
with mcte ( addr ) as (
select 'ILLD/ELKJS/00000000/ELKJS/FHSH' from dual union all
select 'ILLD/EFECTE/0116988-7-002/ADFA/ADFG' from dual union all
select 'IIODK/1573230-0/2216755-7/' from dual union all
select 'IIODK/1573230-0/2216755-700/WRITE' from dual union all
select 'IIODK/TEST/1573230-0/2216755-700/WRITE' from dual union all
select '1234567-8' from dual union all
select '1234567-8/9876543-2' from dual union all
select '1234567-8/TEST/9876543-2' from dual
)
select addr,
REGEXP_SUBSTR(addr,'(^|/)(\d+-\d)(/|$)',1,1,NULL,2) AS num1,
REGEXP_SUBSTR(addr,'(^|/)\d+-\d(/.+?)?/(\d+-\d)(/|$)',1,1,NULL,3) num2
from mcte;
Outputs:
ADDR NUM1 NUM2
-------------------------------------- ------------------- ------------------
ILLD/ELKJS/00000000/ELKJS/FHSH
ILLD/EFECTE/0116988-7-002/ADFA/ADFG
IIODK/1573230-0/2216755-7/ 1573230-0 2216755-7
IIODK/1573230-0/2216755-700/WRITE 1573230-0
IIODK/TEST/1573230-0/2216755-700/WRITE 1573230-0
1234567-8 1234567-8
1234567-8/9876543-2 1234567-8 9876543-2
1234567-8/TEST/9876543-2 1234567-8 9876543-2