Oracle中数字与字符串比较及NULL值处理的查询问题

这是个典型的混合类型+NULL值的比较问题，我来帮你梳理解决方案：

首先明确需求：从视图VW_STATUSNEW中找出STATUS_1和STATUS_2不匹配的所有行。这里的「匹配」定义为：

• 两个值完全相同（包括都是'FAIL'或同一个数字）
• 两个值都是NULL
  除此之外的所有情况都属于不匹配，也就是你要的目标结果。

先回顾你的示例数据 #

ID STATUS_1 STATUS_2
1 FAIL     1
2 FAIL     NULL
3 1        NULL
4 NULL     2
5 2        2
6 2        FAIL
7 NULL     NULL

需要排除ID=5（两个都是2）和ID=7（两个都是NULL），剩下的就是期望返回的行。

解决方案SQL #

提供两种可行写法，按需选择：

写法一：分情况处理，可读性强

SELECT ID, STATUS_1, STATUS_2
FROM VW_STATUSNEW
WHERE
    -- 情况1：一个为NULL，另一个不为NULL
    (STATUS_1 IS NULL AND STATUS_2 IS NOT NULL)
    OR (STATUS_1 IS NOT NULL AND STATUS_2 IS NULL)
    -- 情况2：两个都不为NULL，但内容/类型不匹配
    OR (
        STATUS_1 IS NOT NULL 
        AND STATUS_2 IS NOT NULL
        AND NOT (
            -- 要么都是数字且数值相等，要么都是'FAIL'
            (REGEXP_LIKE(STATUS_1, '^[0-9]+$') AND REGEXP_LIKE(STATUS_2, '^[0-9]+$') 
             AND TO_NUMBER(STATUS_1) = TO_NUMBER(STATUS_2))
            OR STATUS_1 = STATUS_2
        )
    );

写法二：利用Oracle函数简化逻辑

SELECT ID, STATUS_1, STATUS_2
FROM VW_STATUSNEW
WHERE
    -- 用NVL2把NULL转成统一标记，同时统一数字的字符串格式
    NVL2(STATUS_1, CASE WHEN REGEXP_LIKE(STATUS_1, '^[0-9]+$') THEN TO_CHAR(TO_NUMBER(STATUS_1)) ELSE STATUS_1 END, 'NULL_FLAG')
    !=
    NVL2(STATUS_2, CASE WHEN REGEXP_LIKE(STATUS_2, '^[0-9]+$') THEN TO_CHAR(TO_NUMBER(STATUS_2)) ELSE STATUS_2 END, 'NULL_FLAG');

关键细节说明 #

1. 类型不匹配处理：因为STATUS_1和STATUS_2混合了字符串'FAIL'和数字，直接用=比较会报错（比如'FAIL'和1比较）。所以先用正则判断是否为纯数字，是数字则转成数值再比较，非数字直接用字符串比较。
2. NULL值处理：Oracle中NULL和任何值比较都会返回NULL，无法被WHERE条件筛选，因此要么单独判断「一NULL一非NULL」的情况，要么用NVL2把NULL替换成统一标记，让它能和非NULL值做有效比较。
3. 匹配的边界：只有当两个值是相同数字、都是'FAIL'、或者都是NULL时，才视为匹配，其余情况都属于不匹配。

内容的提问来源于stack exchange，提问作者bd528