Oracle VARCHAR2 欄位數值排序錯誤：字典序陷阱與 CAST 轉型解決方案

Oracle VARCHAR2 欄位數值排序錯誤：字典序陷阱與 CAST 轉型解決方案

問題現場

表 CODE_TABLE 欄位 CODE VARCHAR2(20) 存儲純數值字串：'1','2','10','11','20'。

SELECT code FROM code_table ORDER BY code;
-- 結果 (字典序)：
1
10
11
2
20  -- 錯誤！期望 1,2,10,11,20

根因

VARCHAR2 排序遵循字元編碼順序 (字典序)：**逐字元比較**，'1' < '2'，故 '10' 排在 '2' 前面。

解決方案：CAST / TO_NUMBER 轉型

-- 方案 1：CAST (ANSI 標準，推薦)
SELECT code FROM code_table ORDER BY CAST(code AS INTEGER);

-- 方案 2：TO_NUMBER (Oracle 專用)
SELECT code FROM code_table ORDER BY TO_NUMBER(code);

-- 方案 3：LPAD 補零 (純字串排序變通)
SELECT code FROM code_table ORDER BY LPAD(code, 10, '0');

效能考量

• 轉型函數會導致無法使用該欄位上的普通索引 (除非建立函數索引 CREATE INDEX idx_code_num ON code_table(TO_NUMBER(code)))。
• 資料量大時，建議：
1. 調整應用層存儲為 NUMBER 型別 (根治)。
2. 或新增虛擬欄位 CODE_NUM GENERATED ALWAYS AS (TO_NUMBER(CODE)) 並建索引 (11g+)。
3. 若必須查詢排序，確保結果集較小 (分頁查詢)。

進階：混合字母數字排序 (自然排序)

-- 例：'A1','A2','A10','B1' → 正確順序 A1,A2,A10,B1
SELECT code FROM mixed_table
ORDER BY
  REGEXP_SUBSTR(code, '^[A-Za-z]+'),           -- 字母前綴
  TO_NUMBER(REGEXP_SUBSTR(code, '\d+$')),      -- 尾部數字
  code;                                        -- 兜底