函數語法
TO_CHAR(n [, fmt [, 'nlsparam']])
-- n: 數字或日期
-- fmt: 格式模板
-- nlsparam: NLS_NUMERIC_CHARACTERS='dg' NLS_CURRENCY='txt' NLS_ISO_CURRENCY=territory
一、數字格式化核心模板元素
| 元素 | 含義 | 示例 |
|---|
9 | 數字位（前導零不顯示） | TO_CHAR(123, '9999') → ' 123' |
0 | 強制顯示位（前導零顯示） | TO_CHAR(123, '0000') → '0123' |
$ | 貨幣符號（由 NLS 決定） | TO_CHAR(123, '$999') → '$123' |
, | 組分隔符（千分位） | TO_CHAR(1234, '9,999') → '1,234' |
. | 小數點 | TO_CHAR(12.3, '99.99') → '12.30' |
FM | Fill Mode：抑制前導/尾隨空格與零 | TO_CHAR(12.3, 'FM99.99') → '12.3' |
B | 零值顯示為空白 | TO_CHAR(0, 'B999') → ' ' |
MI | 負號後綴 | TO_CHAR(-12, '99MI') → '12-' |
PR | 負數用尖括號 | TO_CHAR(-12, '99PR') → '<12>' |
EEEE | 科學記數法 | TO_CHAR(1234, '9.999EEEE') → '1.234E+03' |
V | 乘以 10^n（n 為 9 的個數） | TO_CHAR(123, '999V99') → '12300' |
二、數字格式化實戰示例
TO_CHAR(1210.73, '9999.9') → '1210.7'
TO_CHAR(1210.73, '9,999.99') → '1,210.73'
TO_CHAR(1210.73, '$9,999.00') → '$1,210.73'
TO_CHAR(21, '000099') → '000021'
TO_CHAR(0.123, '0.999') → '0.123' -- FM 可去除前導空格
TO_CHAR(100.12, '0.999') → '#####' -- 格式位不足溢出顯示 #
TO_CHAR(1.12, '0.999') → '1.120' -- 強制補零
三、日期格式化參數大全
| 參數 | 含義 | 示例 |
|---|
YYYY | 4 位年份 | 2024 |
YY/Y | 後 2/1 位年份 | 24 / 4 |
MM | 月份 (01-12) | 07 |
MON | 月份縮寫 | JUL |
MONTH | 月份全稱（填充至 9 字符） | JULY |
RM | 羅馬數字月份 | VII |
DD | 月內天 (01-31) | 09 |
DDD | 年內天 (1-366) | 191 |
DAY | 星期全稱 | TUESDAY |
DY | 星期縮寫 | TUE |
D | 星期數 (1-7) | 3 |
HH24 | 24 制小時 (0-23) | 14 |
HH12 | 12 制小時 (1-12) | 02 |
MI | 分鐘 (0-59) | 30 |
SS | 秒 (0-59) | 45 |
FF/FF3 | 小數秒 / 毫秒 | .123 |
J | 儒略日 | 2460523 |
Q | 季度 (1-4) | 3 |
WW/IW | 年內週 / ISO 週 | 28 |
W | 月內週 | 2 |
YEAR | 年份拼寫 | TWENTY TWENTY-FOUR |
四、日期格式化實戰示例
TO_CHAR(SYSDATE, 'YYYY/MM/DD') → '2024/07/09'
TO_CHAR(SYSDATE, 'Month DD, YYYY') → 'July 09, 2024'
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') → 'July 9, 2024' -- FM 去除填充空格
TO_CHAR(SYSDATE, 'MON DDth, YYYY') → 'JUL 09TH, 2024'
TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') → 'JUL 9TH, 2024'
TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') → 'Jul 9th, 2024' -- 大小寫控制輸出大小寫
五、NLS 參數自定義數字字符
TO_CHAR(17145, 'L099G999', 'NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')
→ 'NUD017,145' -- L=貨幣符號, G=組分隔符(此處為逗號), 小數點為點
六、實戰避坑指南（PL/SQL 經驗總結）
| 坑點 | 現象 | 解決方案 |
|---|
| 小數點前導零消失 | TO_CHAR(0.123) → '.123' | 使用 '0.999' 或 'FM0.999' 強制顯示整數部 0 |
| 格式位不足顯示 # | TO_CHAR(100.12, '0.999') → '#####' | 擴大整數部格式位：'999.999' 或 'FM999.999' |
| 不想四捨五入只截斷 | TO_CHAR(1.12345, 'FM9.9999') → '1.1235'（四捨五入） | 自寫函數：TRUNC(num, 4) 後再 TO_CHAR，或 FLOOR(num*10000)/10000 |
| 整數卻顯示小數點 | TO_CHAR(100, 'FM90.0999') → '100.' | 用 CASE WHEN num = TRUNC(num) THEN TO_CHAR(num) ELSE TO_CHAR(num, 'FM999.9999') END |
| 日期轉字符串作比較 | WHERE TO_CHAR(dt,'YYYYMMDD') = '20240709' 導致全表掃描 | 改用日期範圍：WHERE dt >= DATE '2024-07-09' AND dt < DATE '2024-07-10' |
| 參數名與列名衝突 | 過程參數 p_name 與表列 name 同名導致解析錯誤 | 參數統一加前綴 p_/v_，引用列時用表別名 t.name |
七、綜合需求實現：小數點最多 4 位、最少 2 位、去空格、四捨五入
-- 1 → '1.00' 1.1 → '1.10' 1.12 → '1.12' 1.1234 → '1.1234' 1.12345 → '1.1235'
SELECT TO_CHAR(num, 'FM9999999.0099') FROM dual;
-- FM: 去空格
-- 9999999: 整數部最多 7 位
-- .00: 至少 2 位小數（不足補 0）
-- 99: 最多再顯示 2 位（共 4 位），第 5 位四捨五入
結語
TO_CHAR 是 Oracle 格式化輸出的瑞士軍刀。掌握 9/0/FM/$/G/D 等核心模板元素、NLS 參數覆蓋、日期大小寫控制，並建立「先定格式模板、再測邊界值」的習慣，即可輕松應對報表導出、介面對接、ETL 轉換等各種字符化場景。記住：數字格式化核心在於「位數佈局」，日期格式化核心在於「模板元素組合」。
