Oracle 字符串含特殊符號 ~ 轉數字並參與運算：DECODE/CASE 表達式處理非數值字符

場景與數據

TABLE: 
  VDEF1 VARCHAR2 -- 默認值 '~'，可能包含數字字符串
  VDEF2 NUMBER
需求：查詢 VDEF2 - VDEF1 = 0 的記錄

核心問題

VDEF1 為 VARCHAR2，直接 VDEF2 - VDEF1 隱式轉換失敗（遇到 '~' 拋 ORA-01722: invalid number）。

解決方案：DECODE / CASE 表達式預處理

方案一：DECODE（Oracle 經典語法）

SELECT * FROM table_name
WHERE VDEF2 - DECODE(VDEF1, '~', 0, VDEF1) = 0;
-- 邏輯：若 VDEF1='~' 則視為 0，否則隱式轉數字參與運算

方案二：CASE 表達式（ANSI SQL 標準，推薦）

SELECT * FROM table_name
WHERE VDEF2 - CASE 
    WHEN VDEF1 = '~' THEN 0
    ELSE TO_NUMBER(VDEF1)
END = 0;

方案三：正則過濾非數字（通用但較重）

SELECT * FROM table_name
WHERE VDEF2 - CASE 
    WHEN REGEXP_LIKE(VDEF1, '^\d+$') THEN TO_NUMBER(VDEF1)
    ELSE 0
END = 0;

性能與最佳實踐建議

1. 優先用 CASE：可讀性強、標準 SQL、優化器友好。
2. 為 VDEF1 建立函數索引（若頻繁查詢）：

   CREATE INDEX idx_vdef1_clean ON table_name (
       CASE WHEN VDEF1 = '~' THEN 0 ELSE TO_NUMBER(VDEF1) END
   );
   

3. 數據治理：從源頭避免「數字列存非數字」，將 '~' 改為 NULL 或單獨標記列 IS_DEFAULT CHAR(1)。

擴展：多種特殊符號統一處理

CASE 
    WHEN VDEF1 IN ('~', '#', '*', '') THEN 0
    ELSE TO_NUMBER(VDEF1)
END

「脏數據進庫，查詢時洗」是常態 —— 用 CASE WHEN 封裝清洗邏輯，讓 SQL 既能跑通、又能利用索引，是工程化的成熟姿態。