-- 查询表中小数位大于4位的数据
select * from tableName where STAT_PERIOD=2012 and REGEXP_LIKE(data_value,'^[+-]?[[:digit:]]*[.]?[[:digit:]]+$') and length(data_value) - length(trunc(data_value)) - 1>4
-- 统计条数
select count(*) from tableName where STAT_PERIOD=2012 and REGEXP_LIKE(data_value,'^[+-]?[[:digit:]]*[.]?[[:digit:]]+$') and length(data_value) - length(trunc(data_value)) - 1>4
-- 列出两条数据分别为正常数据和0开头的数据,做为修改测试:
select *from tableName d where D.VER_ID=295292 and D.PRODUCT_INDEX in (163) and D.BUSINESS_INDEX in (15863) -- 667.8984759529301
select *from tableName d where D.VER_ID=295292 and D.PRODUCT_INDEX in (161) and D.BUSINESS_INDEX in (15863) -- 0.7595529689278261
-- 修改大于4位的小数位 ,本例采用直接截取小数位的方法(trunc),如需四舍五入,则请用round。 to_char 是为了避免修改后0开头的数据出现丢失0的问题。
update tableName set DATA_VALUE= to_char(trunc(DATA_VALUE,4),'fm99999999999999999999999999999999999999990.0000') where STAT_PERIOD=2012 and REGEXP_LIKE(data_value,'^[+-]?[[:digit:]]*[.]?[[:digit:]]+$') and length(data_value) - length(trunc(data_value)) - 1>4