Oracle 判斷字符串是否為數值的自定義函數：TRANSLATE 技巧實現

Oracle 判斷字符串是否為數值的自定義函數：TRANSLATE 技巧實現

Oracle 無內建 ISNUMERIC() 函數，常用 TRANSLATE 技巧實現高效能數值判斷。

核心函數代碼

CREATE OR REPLACE FUNCTION isnumeric (p_str IN VARCHAR2)
RETURN NUMBER
IS
  v_str VARCHAR2(1000);
BEGIN
  IF p_str IS NULL THEN
    RETURN 0;
  ELSE
    -- 關鍵技巧：將所有數字字符與小數點替換為單一字符 '.'
    v_str := TRANSLATE(p_str, '.0123456789', '.');
    
    -- 合法數值轉換後僅剩 '.' 或 '+.' 或 '-.' 或空字串
    IF v_str = '.' 
       OR v_str = '+.' 
       OR v_str = '-.' 
       OR v_str IS NULL THEN
      RETURN 1;  -- 是數值
    ELSE
      RETURN 0;  -- 非數值
    END IF;
  END IF;
END isnumeric;
/

TRANSLATE 函數機制深度解析

語法

TRANSLATE(string, from_str, to_str)

• from_str 中的每個字符對應替換為 to_str 相同位置的字符
• 若 from_str 比 to_str 長，多餘字符在結果中被刪除
• to_str 不能為空 (空字串視為 NULL，導致結果為 NULL)

本函數的替換邏輯

轉換結果範例

⚠️ 原函數邏輯缺陷與修正版

原代碼判斷邏輯 v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL 存在缺陷：

• 純整數 '123' → '...' 不匹配任何條件 → 返回 0 (誤判)
• 多個小數點 '1.2.3' → '....' 不匹配 → 返回 0 (正確但非預期原因)

修正版：支援整數、小數、科學記號、正負號

CREATE OR REPLACE FUNCTION isnumeric_v2 (p_str IN VARCHAR2)
RETURN NUMBER
IS
  v_clean VARCHAR2(1000);
BEGIN
  IF p_str IS NULL THEN
    RETURN 0;
  END IF;
  
  -- 1. 移除前後空白
  v_clean := TRIM(p_str);
  
  -- 2. 正則表達式驗證 (10g+ 推薦，最精確)
  -- 模式：可選正負號 + 數字 + 可選小數部 + 可選指數部
  IF REGEXP_LIKE(v_clean, '^[+-]?(\d+(\.\d*)?|\.\d+)([eE][+-]?\d+)?$') THEN
    RETURN 1;
  END IF;
  
  RETURN 0;
END isnumeric_v2;
/

TRANSLATE 經典應用範例

-- 1. 字符替換映射
SELECT TRANSLATE('abcdefghij', 'abcdef', '123456') FROM dual;
-- 結果: '123456ghij'  (a→1, b→2, c→3, d→4, e→5, f→6)

-- 2. 目標字符集較短：多餘字符被刪除
SELECT TRANSLATE('abcdefghij', 'abcdefghij', '123456') FROM dual;
-- 結果: '123456'  (g,h,i,j 在 from_str 有對應但 to_str 無對應 → 刪除)

-- 3. 刪除特定字符 (to_str 為單一字符)
SELECT TRANSLATE('abcbbaadef', 'ba', '#@') FROM dual;
-- 結果: '@#cc@@@def'  (b→#, a→@)

-- 4. 刪除字符：from_str 有字符但 to_str 無對應
SELECT TRANSLATE('abcbbaadef', 'bad', '#@') FROM dual;
-- 結果: '@#cc@@@ef'  (b→#, a→@, d→刪除)

效能對比

使用建議

1. 高頻呼叫/大量資料：使用修正版 TRANSLATE 邏輯或預先建立函數索引
2. 精確度要求高：優先使用 REGEXP_LIKE (10g+)
3. 避免：TO_NUMBER 捕獲異常的方式，效能極差
4. 12c+：可考慮虛擬欄位 + 函數索引優化查詢