update table set table_column=substr(table_column,1,6);
给你一个样例吧,就是截取小数点“.”后面2个数字吧,可以这样截取的: select substr('120.00000000000000000000000',0,instr('120.00000000000000000000000','.',1,1)+2) from dual
查看一下:(看看是不是你想要的) select substr(table_column,0,instr(table_column,'.')+2) from table; 更新: update table set table_column=substr(table_column,0,instr(table_column,'.')+2);
update tablename set c1= case when instr(c1,'.',1,1)>0 then substr(c1,0,instr(c1,'.',1,1)+2) else c1 end;如果有小数点,就update,截取小数点后面2位,否则不截取、。
超级晕菜还要考虑四舍五入吧对你的案例而言就是:update table test set table_column=round(field_name,2);
SQL> select * from t;COL ---------------------------------------- 120.00000000000000000000000 20.00000000000000000000000SQL> update t set col=to_char(to_number(col),'99999.99');2 rows updated.SQL> select * from t;COL ---------------------------------------- 120.00 20.00SQL>
给你一个样例吧,就是截取小数点“.”后面2个数字吧,可以这样截取的:
select
substr('120.00000000000000000000000',0,instr('120.00000000000000000000000','.',1,1)+2)
from dual
select substr(table_column,0,instr(table_column,'.')+2) from table;
更新:
update table set table_column=substr(table_column,0,instr(table_column,'.')+2);
set c1= case when instr(c1,'.',1,1)>0 then substr(c1,0,instr(c1,'.',1,1)+2)
else c1 end;如果有小数点,就update,截取小数点后面2位,否则不截取、。
set table_column=round(field_name,2);
----------------------------------------
120.00000000000000000000000
20.00000000000000000000000SQL> update t set col=to_char(to_number(col),'99999.99');2 rows updated.SQL> select * from t;COL
----------------------------------------
120.00
20.00SQL>