项目初期表结构设计是非常重要,在字段类型定义样也要格外小心,业务开展后,修改字段类型代价非常大。本文主要记录在 oracle 中 number 类型转换为 varchar2 数据类型方法以及案例演示。
number类型的数据直接存人 varchar2类型的字段中,会出现格式问题,如:.5, 5. 等
to_char(number)
可将 number 类型转换为 varchar2 类型,可以指定格式 fmt(可选);参数n,可以是 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型;nlsparam 参数指定了数值格式的元素返回的字符:包括:小数点字符(d)、组分隔符(g)、本地货币符号、国际货币符号。格式化修饰符 FM
9: 代表如果存在数字则显示数字,不存在数字为空字符串(非FM格式则会补空格);0: 代表如果存在数字则显示数字,不存在数字则显示0;小数会做四舍五入处理,如果不想四舍五入,现将数据做 trunc 处理后,再转换格式;注意点:
转换整数需要注意末尾是否多了小数点;转换小于1的小数首位的0会丢失;数据长度大于 fmt 定义的长度,转换会发生异常;
基本用法
-- 9 不会补占位符select to_char(123.45, 'FM9999.999') from dual;-- ==>-- 补占位符 0select to_char(123.45, 'FM0000.000') from dual;-- ==>0123.450-- 四舍五入select to_char(123.4567, 'FM9999.999') from dual;-- ==>123.457select length(to_char(123.4567, 'FM9999.999')) from dual;-- ==> 7
整数格式化问题
select to_char(123, 'FM9999.999') from dual;-- ==>123.-- 解决方案1select regexp_replace(to_char(123, 'FM9999.999'), '\.$', '') from dual;-- ==>123-- 解决方案2select to_char(123, 'FM9999.099') from dual;-- ==>123.0
小数格式化问题(小于1的数)
建议格式化数值,如果不确定是整数,还是小于1的数,在小数点左右使用 0 作为占位符;-- 小数格式化问题(小于1的数)select to_char(0.123, 'FM9999.999') from dual;-- ==>.123-- 解决方法select to_char(0.123, 'FM9990.999') from dual;-- ==>0.123
数据太长导致的错误
-- 数据太长导致的错误:select to_char(123.4567, 'FM99.9') from dual;-- ==>#####