資料型別轉換是 SQL 開發的高頻操作。Oracle 提供豐富的內建轉換函數，本文整理 **TO_CHAR、TO_DATE、TO_NUMBER、CAST、CONVERT、ASCIISTR、CHARTOROWID、ROWIDTOCHAR** 等核心函數的語法、格式模型、典型用法與避坑指南，供開發隨查隨用。
| 函數 | 方向 | 典型用途 | 關鍵參數 |
|---|---|---|---|
| TO_CHAR | 日期/數字 → 字串 | 報表顯示、匯出、格式化 | 格式模型 (fmt)、NLS 參數 |
| TO_DATE | 字串 → 日期 | ETL 載入、參數解析 | 格式模型、NLS 參數 |
| TO_NUMBER | 字串 → 數字 | 字串運算、資料清洗 | 格式模型、NLS 參數 |
| CAST | 任意內建型別互轉 | 型別標準化、集合轉換 | 目標型別 (含長度/精度) |
| CONVERT | 字串字元集轉換 | 跨字元集遷移、修復亂碼 | 目標字元集、來源字元集 |
| ASCIISTR | 任意字串 → ASCII 轉義 | 非 ASCII 字元可視化、除錯 | 無 |
| CHARTOROWID | 字串 → ROWID | 動態 SQL 綁定 ROWID | 合法 ROWID 字串 |
| ROWIDTOCHAR | ROWID → 字串 | 顯示/記錄 ROWID | ROWID 值 |
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 2024-01-15 14:30:45
SELECT TO_CHAR(SYSDATE, 'FMYYYY-MM-DD') FROM dual; -- 2024-1-15 (無前導零)
SELECT TO_CHAR(SYSDATE, 'YEAR') FROM dual; -- TWENTY TWENTY-FOURSELECT TO_CHAR(1234567.89, 'L999G999D99') FROM dual; -- $1,234,567.89 (依 NLS_CURRENCY)
SELECT TO_CHAR(1234567.89, 'FM999G999D99') FROM dual; -- 1,234,567.89 (無貨幣符號)
SELECT TO_CHAR(0.000123, '9.999EEEE') FROM dual; -- 1.230E-049：數字位 (前導空格)0：數字位 (前導零)FM：移除前導/尾隨空格與零G：群組分隔符 (千分位)D：小數點L：本地貨幣符號C：國際貨幣符號MI：負號後綴 (如 99MI → 99-)PR：負數用角括號 (如 <99>)SELECT TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY') FROM dual;
SELECT TO_DATE('20240115', 'YYYYMMDD') FROM dual;⚠️ 陷阱： 分鐘用 MI，**不可用 MM** (月份)。格式模型不分大小寫。
SELECT TO_NUMBER('1,234,567.89', '999G999D99') FROM dual; -- 1234567.89
SELECT TO_NUMBER('$1,234.56', 'L999G999D99') FROM dual; -- 1234.56
SELECT TO_NUMBER('-123.45', '999D99MI') FROM dual; -- -123.45輸入字串必須符合格式模型，否則拋 ORA-01722: invalid number。
SELECT CAST(SYSDATE AS VARCHAR2(30)) FROM dual; -- 日期→字串
SELECT CAST('123.45' AS NUMBER(10,2)) FROM dual; -- 字串→數字
SELECT CAST(col AS TIMESTAMP WITH TIME ZONE) FROM tbl; -- 跨時區型別
-- 集合轉換
SELECT CAST(MULTISET(SELECT ename FROM emp) AS SYS.ODCIVARCHAR2LIST) FROM dual;優點：ANSI 標準、支援長度/精度/小數位精確控制、支援集合型別。
-- 將 ZHS16GBK 編碼字串轉為 US7ASCII (非 ASCII 字元會變 ? 或丟失)
SELECT CONVERT('中文', 'US7ASCII', 'ZHS16GBK') FROM dual; -- ??
-- 實務用於資料修復：已知錯誤字元集寫入的欄位
UPDATE tbl SET col = CONVERT(col, 'AL32UTF8', 'ZHS16GBK') WHERE ...;參數順序：CONVERT(str, dest_charset, source_charset)。來源字元集可省略 (預設資料庫字元集)。
SELECT ASCIISTR('中文') FROM dual; -- \4E2D\6587 (Unicode 碼點轉義)
SELECT CHARTOROWID('AAAR+AAEAAAAAABTAAA') FROM dual; -- 字串→ROWID
SELECT ROWIDTOCHAR(rowid) FROM emp WHERE rownum=1; -- ROWID→VARCHAR2(18)| 場景 | 風險 | 建議 |
|---|---|---|
| WHERE col = '2024-01-15' (col 為 DATE) | 依賴 NLS_DATE_FORMAT，跨環境失效 | 顯式 TO_DATE('2024-01-15','YYYY-MM-DD') |
| WHERE num_col = '123' (num_col 為 NUMBER) | 隱性 TO_NUMBER，索引可能失效 | 顯式 TO_NUMBER 或綁定變數 |
| SELECT 'Q' || 1 FROM dual | 數字隱性 TO_CHAR，格式不可控 | SELECT 'Q' || TO_CHAR(1, 'FM999') |
所有格式化函數可選第三參數 'NLS_DATE_LANGUAGE=AMERICAN' 等，覆蓋會話級設定，確保跨環境穩定：
SELECT TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE=AMERICAN') FROM dual; -- JANUARY掌握這套轉換函數體系，並養成「顯式轉換、指定格式、隔離 NLS」習慣，即可寫出健壯、可移植、高效能的 Oracle SQL 代碼。
