to_char(num,'fm999999990.999999999')
to_char(num,'fm999999990.000000000')
总之进行0,9的切换尝试组合就行了
如果有时出现 0.% 后面就没有了的情况
可以用replace函数 replace('.%','.00%')
较为通用的,格式化成 0.00%格式的方法
replace(to_char(d.qxxcl*100,'fm999999990.999999999') || '%','.%','.00%')
解决方案如下
(1)
- SQL> select to_nmber(to_char(0.99)) from dual;
-
- TO_NUMBER(TO_CHAR(0.99))
-
- 0.99
-
- SQL> select to_char(num,'fm999999990.999999999') from ml_test;
-
- TO_CHAR(NUM,'FM999999990.99999
-
- 0.42124
- 0.421246543
- 65432.42124324
- 4.62124
(2)使用case when 或 decode函数,取第一位是否为".", 补0 ,就ok了
- SQL> select to_char(num,'999999999.999999999') from ml_test;
-
- TO_CHAR(NUM,'999999999.9999999
-
- .421240000
- .421246543
- 65432.421243240
- 4.621240000
-
- SQL> select to_char(num) from ml_test;
-
- TO_CHAR(NUM)
-
- .42124
- .4212465434
- 65432.42124324
- 4.62124
-
- SQL> select to_char(0.99) from dual;
-
- TO_CHAR(0.99)
-
- .99
-