--oracle10g
create table BREAKOUT
(
OBJECTID INTEGER not null,
GJ NVARCHAR2(10),
RWIDTH NUMBER(38,8),
RHEIGHT NUMBER(38,8)
);insert into BREAKOUT (OBJECTID,GJ,Rwidth,Rheight) values (1,'500X600',0,0);
insert into BREAKOUT (OBJECTID,GJ,Rwidth,Rheight) values (2,'700X600',0,0);
insert into BREAKOUT (OBJECTID,GJ,Rwidth,Rheight) values (3,'300X400',0,0);
insert into BREAKOUT (OBJECTID,GJ,Rwidth,Rheight) values (4,'450',0,0);
insert into BREAKOUT (OBJECTID,GJ,Rwidth,Rheight) values (4,'550',0,0);select * from BREAKOUT;我想把GJ这个字段,拆成2个数值,第一个数值放在RWIDTH里边,第二个数值放在RHEIGHT里边;而且,如果没有X的话,默认放到RWIDTH里边,请问,该怎么写UPDATE语句呢?
update BREAKOUT
set RWIDTH = (case when instr(GJ, 'X') > 0 then to_number(substr(GJ, 1, instr(GJ, 'X') - 1)) else to_number(GJ) end),
RHEIGHT =(case when instr(GJ, 'X') > 0 then to_number(substr(GJ, instr(GJ, 'X') + 1)) else RHEIGHT end);
commit;
不过要求 oracle 在 10g 以上
update BREAKOUT
set Rwidth = nvl(regexp_substr(GJ, '[^X]+', 1, 1), Rwidth),
Rheight = nvl(regexp_substr(GJ, '[^X]+', 1, 2), Rheight);
commit;