日期:2014-05-16  浏览次数:21215 次

ORACLE中如何拆分一个字符串字段为2个数值字段?
SQL code

--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);

select * from BREAKOUT;


我想把GJ这个字段,拆成2个数值,第一个数值放在RWIDTH里边,第二个数值放在RHEIGHT里边,请问,该怎么写UPDATE语句呢?

------解决方案--------------------
SQL code
update breakout set RWIDTH=substr(gj,1,instr(gj,'X')-1),RHEIGHT=substr(gj,instr(gj,'X')+1,length(gj)-instr(gj,'X'));
commit;

------解决方案--------------------
SQL code


--是不是这个意思??
create table BREAKOUT
(
  OBJECTID     INTEGER not null,
  GJ           NVARCHAR(10),
  RWIDTH       NUMERIC(38,8),
  RHEIGHT      NUMERIC(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);


UPDATE BREAKOUT
SET RWIDTH=LEFT(GJ,CHARINDEX('X',GJ)-1),
RHEIGHT=RIGHT(GJ,LEN(GJ)-CHARINDEX('X',GJ))

SELECT * FROM BREAKOUT
---

OBJECTID    GJ    RWIDTH    RHEIGHT
1    500X600    500.00000000    600.00000000
2    700X600    700.00000000    600.00000000
3    300X400    300.00000000    400.00000000