Oracle NUMBER 轉 CHAR 遺失前導零問題與格式化解決方案
Oracle TO_CHAR(number) 預設不保留整數部為 0 的前導零（如 0.1 → '.1'），導致前端顯示或介接系統解析異常。本文說明現象、標準格式模型解決方案，與利用 REPLACE 兼容整數/小數混雜場景的實用技巧。
一、現象重現
SELECT TO_CHAR(0.1) nums FROM dual; -- 顯示 .1 (遺失前導 0)
SELECT TO_CHAR(1) nums FROM dual; -- 顯示 1 (整數變科學標記風格)
二、標準解決：格式模型 (Format Model)
SELECT TO_CHAR(0.1, '0.9') nums FROM dual; -- 顯示 0.1
SELECT TO_CHAR(1, '0.9') nums FROM dual; -- 顯示 1.0 (含小數點)
0：強制顯示數字，不足補 0。9：顯示數字，不足留空白。FM：移除前後空白（FM0.9）。
三、常用格式模板
| 需求 | 格式字串 | 範例輸入 | 輸出 |
|---|
| 固定 2 位小數 | FM999999990.00 | 123.4 | 123.40 |
| 千分位分隔 + 2 位小數 | FM999,999,990.00 | 12345.6 | 12,345.60 |
| 科學記號 | 9.99EEEE | 1234 | 1.23E+03 |
| 貨幣符號 | L999,999.00 | 1234.5 | NT$1,234.50 (視 NLS_CURRENCY) |
四、混合整數/小數欄位的通用處理技巧
若欄位可能為整數或小數，且需「整數不顯示小數點、小數保留兩位」，可結合 REPLACE：
SELECT unitname,
hurk, ldrk,
REPLACE(TO_CHAR(ROUND(hurk/ldrk,2), 'FM999999990.00'), '.00', '') AS scale,
rdate
FROM dynamicpeople;
ROUND(...,2) 先四捨五入兩位。TO_CHAR(...,'FM999999990.00') 強制產生兩位小數 (如 1.50、2.00)。REPLACE(... , '.00', '') 移除整數尾部的 .00，得到 1.5、2。
五、效能與相容性提醒
TO_CHAR 為 CPU 密集操作，大量資料建議在應用層格式化或使用虛擬欄位 GENERATED ALWAYS AS (TO_CHAR(...)) VIRTUAL。- NLS 參數 (
NLS_NUMERIC_CHARACTERS) 影響小數點/千分位字元，跨環境部署請顯式指定格式模型，勿依賴預設值。
掌握格式模型與 REPLACE 組合技，即可彈性處理各類數值顯示需求，避免前導零遺失與多餘小數點干擾。
- 上一篇文章： Oracle 日期範圍查詢陷阱：TO_DATE 排除邊界 vs TO_CHAR 包含邊界
- 下一篇文章： Oracle 會話與效能分析 23 條核心 SQL：等待事件、鎖、緩存命中率、CPU、熱點物件
Copyright ©
程式碼之家 All Rights Reserved
