当前位置: 代码迷 >> Oracle技术 >> ORACLE中怎么根据条件拆分一个字符串字段为2个数值字段
  详细解决方案

ORACLE中怎么根据条件拆分一个字符串字段为2个数值字段

热度:279   发布时间:2016-04-24 08:41:26.0
ORACLE中如何根据条件拆分一个字符串字段为2个数值字段?
SQL code
--oracle10gcreate 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语句呢?

------解决方案--------------------
SQL code
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;
  相关解决方案