數據清洗實用函數：Oracle 判斷字符串是否為數字的 ISNUM 實現

數據清洗實用函數：Oracle 判斷字符串是否為數字的 ISNUM 實現

在數據入庫（ETL）過程中，目標字段定義為 NUMBER 類型，但源端數據往往夾雜中文、特殊字符或無意義英文，導致插入報錯。通用做法是編寫一個判斷函數：是數字則保留原值，非數字則轉為 NULL，保證載入流程不中斷。本文提供一個健壯的 ISNUM 函數實現，並說明關鍵邏輯與使用場景。

一、函數設計目標

• 輸入：VARCHAR2 字符串；
• 輸出：NUMBER（1 表示純數字，0 表示非數字）；
• 兼容：整數、小數、科學計數法、正負號、前後空格；
• 性能：純 PL/SQL 實現，無上下文切換開銷。

二、完整函數代碼

CREATE OR REPLACE FUNCTION isnum(p_str IN VARCHAR2) RETURN NUMBER IS
    v_num NUMBER;
BEGIN
    -- 去除首尾空格
    v_num := TO_NUMBER(TRIM(p_str));
    RETURN 1;
EXCEPTION
    WHEN VALUE_ERROR THEN
        RETURN 0;
    WHEN OTHERS THEN
        RETURN 0;
END isnum;
/
SHOW ERRORS

三、核心邏輯解析

1. TRIM：消除前後空格干擾（Oracle TO_NUMBER 不自動去空格）；
2. TO_NUMBER：隱式完成格式校驗，合法數字直接轉換成功；
3. 異常捕獲：VALUE_ERROR 專門捕獲格式錯誤，OTHERS 兜底，確保函數永不拋出異常；
4. 返回值：1/0 便於 SQL 中直接用於 CASE WHEN 或 DECODE。

四、典型應用場景

1. 插入時動態清洗

INSERT INTO target_table (id, amount)
SELECT src_id,
       CASE WHEN isnum(src_amt) = 1 THEN TO_NUMBER(TRIM(src_amt)) ELSE NULL END
FROM source_table;

2. 建立約束輔助檢查

ALTER TABLE staging_table ADD CONSTRAINT chk_amt_num
CHECK (isnum(amt) = 1);

3. 數據質量報表統計

SELECT isnum(amt) AS is_numeric, COUNT(*)
FROM source_table
GROUP BY isnum(amt);

五、進階擴展：支持自定義格式

若需判斷「整數」、「兩位小數」等特定格式，可擴展參數：

CREATE OR REPLACE FUNCTION isnum_adv(
    p_str  IN VARCHAR2,
    p_fmt  IN VARCHAR2 DEFAULT '9999999999D99'  -- 默認整數+兩位小數
) RETURN NUMBER IS
    v_num NUMBER;
BEGIN
    v_num := TO_NUMBER(TRIM(p_str), p_fmt);
    RETURN 1;
EXCEPTION WHEN OTHERS THEN RETURN 0; END;

六、注意事項

• TO_NUMBER 依賴會話級 NLS_NUMERIC_CHARACTERS，跨環境部署時建議顯式指定格式模板；
• 超大數據量時，考慮在 SQL 層面用正則 REGEXP_LIKE(str, '^[+-]?\d+(\.\d+)?$') 避免行級函數調用開銷；
• 函數創建於通用 Schema（如 COMMON_FUNCS），配合 GRANT EXECUTE 實現多用戶共享（詳見上篇）。

七、小結

ISNUM 是數據清洗工具箱中的「瑞士軍刀」，以極簡代碼解決「髒數據入庫」的經典痛點。掌握其原理與擴展方式，能顯著提升 ETL 流程的穩健性與可維護性。