create table sx(
nd varchar2(20),
xm varchar2(20),
nm number(12,6)
)insert into sx select '2012','A',12.2222 from dual
insert into sx select '2012','A01',12.2412 from dual;
insert into sx select '2012','B0445',12.2612 from dual;
insert into sx select '2012','B0316162',12.9212 from dual;
insert into sx select '2012','B03161621',12.7212 from dual;结果 根据XM长度拆分 为4列,如果不够4列,置为'*'
"
if(length(@XM) = 1) {
@CYLX = @XM1.substr(0,1);
}
else if(length(@XM) = 3) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
}
else if(length(@XM) = 5) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
@HYDL = @XM.substr(3,2);
}
else if(length(@XM) > 5) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
@HYDL = @XM.substr(3,2);
@HYDL = @XM.substr(5,XM1.length-5);
}
"
想要结果为 2012,A,*,*,*,,NM
2012,A,01,*,*,NM
2012,B,04,45,*,NM
nd varchar2(20),
xm varchar2(20),
nm number(12,6)
)insert into sx select '2012','A',12.2222 from dual
insert into sx select '2012','A01',12.2412 from dual;
insert into sx select '2012','B0445',12.2612 from dual;
insert into sx select '2012','B0316162',12.9212 from dual;
insert into sx select '2012','B03161621',12.7212 from dual;结果 根据XM长度拆分 为4列,如果不够4列,置为'*'
"
if(length(@XM) = 1) {
@CYLX = @XM1.substr(0,1);
}
else if(length(@XM) = 3) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
}
else if(length(@XM) = 5) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
@HYDL = @XM.substr(3,2);
}
else if(length(@XM) > 5) {
@CYLX = @XM.substr(0,1);
@HYML = @XM.substr(1,2);
@HYDL = @XM.substr(3,2);
@HYDL = @XM.substr(5,XM1.length-5);
}
"
想要结果为 2012,A,*,*,*,,NM
2012,A,01,*,*,NM
2012,B,04,45,*,NM
select nd,
case
when length(xm)=1 then xm || ',*,*,*'
when length(xm)=3 then regexp_replace(xm, '(.)(..)', '\1,\2,*,*')
when length(xm)=5 then regexp_replace(xm, '(.)(..)(..)', '\1,\2,\3,*')
when length(xm)>5 then regexp_replace(xm, '(.)(..)(..)(.+)', '\1,\2,\3,\4')
else null
end xm_split,
nm
from sx;