数据库中的subtittle数据是这样的BR.777-1
BS.6/175
D.140 Supplement 1
E.191(2000) Amendment 1现在想将它折成三部分,即.号前的字母为第一部分, .号后的数字为第二部分,数字后的为第三部分
如E.191(2000) Amendment 1,
第一部分为 E
第二部分为 191
第三部分为 (2000) Amendment 1怎么样才能实现??
BS.6/175
D.140 Supplement 1
E.191(2000) Amendment 1现在想将它折成三部分,即.号前的字母为第一部分, .号后的数字为第二部分,数字后的为第三部分
如E.191(2000) Amendment 1,
第一部分为 E
第二部分为 191
第三部分为 (2000) Amendment 1怎么样才能实现??
with t as
(select 'aa.191(2000) Amendment 1' subtitle from dual)
select
substr(subtitle,1,instr(subtitle,'.')-1) a,
substr(REGEXP_SUBSTR(subtitle,'\.([0-9]*)'),2) b,
substr(subtitle,REGEXP_INSTR(subtitle,'\.([0-9]*)',1,1,1))
from t;
select 'BR.777-1' A from dual
union all
select 'BS.6/175' A from dual
union all
select 'D.140 Supplement 1' A from dual
union all
select 'E.191(2000) Amendment 1' A from dual
)
select substr(A,0,instr(A,'.')-1),
substr(A,instr(A,'.') + 1,regexp_instr(A,'[^.[:alnum:]]') - (instr(A,'.') + 1)),
substr(substr(A,instr(A,'.') + 1,length(A) - instr(A,'.')),regexp_instr(substr(A,instr(A,'.') +1,length(A) - instr(A,'.')),'[^[:alnum:]]'))
from temp
substr('BR.777-1',instr('BR.777-1','.')+1,regexp_instr('BR.777-1','[[:blank:]]|[[:punct:]]',1,2)-1-instr('BR.777-1','.')),
substr('BR.777-1',regexp_instr('BR.777-1','[[:blank:]]|[[:punct:]]',1,2),length('BR.777-1')-(regexp_instr('BR.777-1','[[:blank:]]|[[:punct:]]',1,2)-1))
from dual;
SU SUB SU
-- --- --
BR 777 -1