Oracle SQL提取子串生成数值列遇丢失行等问题求助

核心问题分析 #

行丢失的本质是隐式转换（+0）或直接用TO_NUMBER时，存在无法转为数字的字符串，Oracle会直接过滤这类行（或抛出转换错误）。另外你的COD4提取逻辑存在明显错误（误引用COD3），需要同步修正。

统一修正后的单字段查询模板（以COD5为例） #

针对每个COD字段，先确保提取内容为合法数字，再安全转为数值，同时处理"Nothing"、缺失COD、NULL等异常情况：

SELECT 
    DFI6,
    TO_NUMBER(
        CASE 
            WHEN DFI6 LIKE '%COD5%' THEN
                CASE
                    -- 匹配"Nothing"或其他非数字内容，或提取结果为NULL
                    WHEN REGEXP_LIKE(SUBSTR(DFI6, INSTR(DFI6, 'COD5=') + 5), '^[^0-9.-]+')
                    OR SUBSTR(DFI6, INSTR(DFI6, 'COD5=') + 5) IS NULL
                    THEN '-1'
                    ELSE SUBSTR(DFI6, INSTR(DFI6, 'COD5=') + 5)
                END
            ELSE '-1'
        END,
        '9999999999', -- 根据你的数值范围调整格式
        'ON CONVERSION ERROR -1' -- 转换失败时返回-1，Oracle 12.2+支持
    ) AS COD5
FROM myTable
WHERE updated_date > SYSDATE - 300
AND DFI6 IS NOT NULL;

COD4的修正查询 #

原查询误用COD3导致提取逻辑错误，修正后：

SELECT 
    DFI6,
    TO_NUMBER(
        CASE 
            WHEN DFI6 LIKE '%COD4%' THEN
                CASE
                    WHEN REGEXP_LIKE(SUBSTR(DFI6, INSTR(DFI6, 'COD4=') + 5), '^[^0-9.-]+')
                    OR SUBSTR(DFI6, INSTR(DFI6, 'COD4=') + 5) IS NULL
                    THEN '-1'
                    -- 提取COD4值，直到下一个|分隔符或字符串结尾
                    ELSE SUBSTR(
                        DFI6, 
                        INSTR(DFI6, 'COD4=') + 5,
                        NVL(INSTR(DFI6, '|', INSTR(DFI6, 'COD4=')), LENGTH(DFI6)+1) - INSTR(DFI6, 'COD4=') -5
                    )
                END
            ELSE '-1'
        END,
        '9999999999',
        'ON CONVERSION ERROR -1'
    ) AS COD4
FROM myTable
WHERE updated_date > SYSDATE - 30
AND DFI6 IS NOT NULL;

关键优化点 #

• 解决行丢失问题：用TO_NUMBER的ON CONVERSION ERROR参数确保转换失败时返回-1而非丢弃行；若你的Oracle版本确实不支持该参数，可嵌套CASE用正则验证合法性：

  CASE
      WHEN REGEXP_LIKE(your_string, '^-?\d+(\.\d+)?$') THEN TO_NUMBER(your_string)
      ELSE -1
  END AS CODn
  

• 简化提取逻辑：用INSTR(DFI6, 'CODn=') + 5直接定位COD值起始位置（"CODn="长度为5），避免多层SUBSTR嵌套；
• 统一异常处理：通过CASE将"Nothing"、NULL、非数字内容全部转为-1；
• 批量提取建议：单个字段测试通过后，合并为一个查询，避免多次扫描大表（模板可参考上方COD1/COD2的写法）。

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