SQL正则提取orderstatus日期报MM值无效问题求解

原SQL逻辑存在3个核心缺陷，直接触发报错和匹配失败：

• 正则规则仅匹配mm/dd两段数字，未覆盖完整的mm/dd/yy/mm/dd/yyyy三段日期结构：当*SLD开头的记录中不存在任何斜杠时，REGEXP_SUBSTR返回NULL，拼接/2022后得到类似/2022的非法字符串，传入TO_DATE时MM位无有效值直接触发报错；当记录中存在完整日期时，仅截取前两段mm/dd硬拼2022，会忽略字段中自带的年份值导致解析错误
• 未对*SLD开头但无合法日期的场景做判空处理，REGEXP_SUBSTR返回NULL时直接拼接字符串传入TO_DATE，必然触发格式报错
• 硬编码拼接2022作为年份，完全没有处理字段中自带的两位/四位年份值，会导致年份解析错误

调整正则规则完整匹配三段式日期，增加匹配结果判空逻辑，直接使用字段中自带的年份做日期解析，不再硬编码年份值。修正后SQL如下：

CASE 
    WHEN REGEXP_COUNT("ORDERSTATUS", '^\\*SLD') <> 0 
    THEN 
        -- 提取完整的mm/dd/yy或mm/dd/yyyy格式日期，匹配不到则回退取ORDDATE
        COALESCE(
            TO_DATE(
                REGEXP_SUBSTR("ORDERSTATUS", '(^|[^0-9])([0-9]{1,2}/[0-9]{1,2}/([0-9]{2}|[0-9]{4}))([^0-9]|$)', 1, 1, 'c', 2),
                'MM/DD/YYYY'
            ),
            DATE("ORDDATE")
        )
    ELSE DATE("ORDDATE")
END AS soldorstockdate

• 针对无合法日期的*SLD开头记录（如*SLD BOBBY LANE IDS#2347509、*SLD S.STONE STK# 39391 40183），正则无法匹配到完整三段日期时会直接返回NULL，通过COALESCE回退取ORDDATE字段值，不会触发TO_DATE格式报错
• 针对带两位年份的日期记录（如*SLD F.CHAPMAN/S.GUNN 5/31/22、*SLD BAM 6/9/22），正则会完整提取整个日期串，TO_DATE可自动识别两位年份对应的世纪（默认00-29映射2000-2029，30-99映射1930-1999，符合业务中22对应2022的需求），不会出现只截取前半段日期的问题
• 正则增加了前后非数字边界校验，不会误匹配ID、库存编码等字段中零散的数字斜杠组合

若业务对两位年份的世纪映射有特殊规则，可调整TO_DATE的格式模板，或对提取到的两位年份做单独拼接处理即可。

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