- 需求
字段值含有“+”,以"+“将字段值分开。将下述样例中A字段,拆分成六个字段(即6列),因为值中”+"最多为5个。
需求样例:
A
1 ADSA++FSAEV
2 DFAW+1FDA+423FSAFA
3 DFASVA+DFABTBG+31214VCX
4 DAFUKY+YTHF6+TFHN7
5 TDJ64+HGJD3
6 STJ+YJ6775+HJKMNF+765FV+HFMGHJ+JSTYSTY
7 1+2++3+4+5
- 测试数据准备
create table testsss
(
a VARCHAR2(255)
);
insert into testsss (A)
values ('ADSA++FSAEV');
insert into testsss (A)
values ('DFAW+1FDA+423FSAFA');
insert into testsss (A)
values ('DFASVA+DFABTBG+31214VCX');
insert into testsss (A)
values ('DAFUKY+YTHF6+TFHN7');
insert into testsss (A)
values ('TDJ64+HGJD3');
insert into testsss (A)
values ('STJ+YJ6775+HJKMNF+765FV+HFMGHJ+JSTYSTY');
insert into testsss (A)
values ('1+2++3+4+5');
commit;
- 解决方案一:
select testsss.a||'+',
substr(testsss.a||'+', 1, instr(testsss.a||'+', '+',1,1) - 1),
substr(testsss.a||'+', instr(testsss.a||'+','+', 1, 1)+1, instr(testsss.a||