1.使用 TO_CHAR处理小数
SELECT TO_CHAR(1233,'fm9999999990.00') FROM DUAL; 结果 1233.00
SELECT TO_CHAR(1233.096,'fm9999999990.00') FROM DUAL; 结果 1233.10
SELECT TO_CHAR(0.0237,'fm9999999990.00') FROM DUAL; 结果 0.02
SELECT TO_CHAR(1233,'fm9999999999.99') FROM DUAL; 结果 1233.
SELECT TO_CHAR(1233.096,'fm9999999999.99') FROM DUAL; 结果 1233.1
SELECT TO_CHAR(0.0237,'fm9999999999.99') FROM DUAL; 结果 .02
①其9代表:如果存在数字则显示数字,不存在则显示空格
②其0代表:如果存在数字则显示数字,不存在则显示0,即占位符。
③其FM代表:删除如果是因9带来的空格,则删除之
可以看出,该处理方式有四舍五入作用。
所以保留两位小数应该使用的格式为SELECT TO_CHAR(1233.096,'fm9999999990.00') FROM DUAL;
SELECT TO_CHAR(1.00,'99999990.00'), TO_CHAR(1.00,'FM99999990.00') FROM DUAL; 结果为 1.00和1.00
SELECT LENGTH(TO_CHAR(1.00,'99999990.00')), LENGTH(TO_CHAR(1.00,'FM99999990.00')) FROM DUAL; 结果为 12 和4
由上可以明显看出:FM 可以删除由9带来的空格。
2.使用 TO_CHAR整理数字格式
SELECT TO_CHAR(12934321234,'999999,999,999') FROM DUAL; 12934,321,234
SELECT TO_CHAR(12934321234,'FM999999,999,999') FROM DUAL; 12934,321,234
SELECT TO_CHAR(12934321234,'FM999999,999,999.00') FROM DUAL; 12934,321,234.00
SELECT TO_CHAR(12934321234,'FM999999,999,999.99') FROM DUAL; 12934,321,234.
SELECT TO_CHAR(12934321234,'99999,999,999.99') FROM DUAL; 12934,321,234.00
SELECT TO_CHAR(12934321234.237,'FM999999,999,999.99') FROM DUAL; 12934,321,234.24
SELECT TO_CHAR(12934321234.237,'FM999999,999,999.00') FROM DUAL; 12934,321,234.24