Oracle ORA-01722报错：正则取值转数字及escalation_level匹配问题

问题1：安全转换提取值为数字实现正确比较 #

你遇到的ORA-01722报错根因为部分记录的escalation_level字段末尾提取结果包含非数字字符（如末尾逗号、空值、非数字标记等），无法直接转为数值，可通过以下两种方式解决：

• 方式1：使用正则精准匹配末尾数字+安全转换（支持Oracle 12cR2及以上版本）
  优化正则表达式仅匹配末尾的纯数字序列，同时使用带转换容错的TO_NUMBER函数避免报错，WHERE子句修改为：

  WHERE TO_NUMBER(
    REGEXP_SUBSTR(ESCALATION_LEVEL, '(\d+)$', 1, 1, NULL, 1) 
    DEFAULT -1 ON CONVERSION ERROR
  ) <= 输入参数
  

  说明：REGEXP_SUBSTR的第6个参数指定返回正则第一个分组的内容，确保只提取末尾的数字序列；DEFAULT -1 ON CONVERSION ERROR表示如果转换失败返回-1，自动过滤不符合格式的记录，也可根据业务需求调整默认值。
• 方式2：先过滤可转换为数字的记录再转换（兼容更低版本Oracle）
  先通过VALIDATE_CONVERSION判断提取结果是否可转为数字，再执行转换：

  WHERE VALIDATE_CONVERSION(REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS NUMBER) = 1
  AND CAST(REGEXP_SUBSTR(ESCALATION_LEVEL, '[^,]*$') AS NUMBER) <= 输入参数
  

问题2：无需转数字的替代实现方案 #

如果数据库版本不支持上述转换容错函数，可使用左补零对齐的字符串比较方案，完全规避数值转换报错，同时解决ASCII排序问题：

WHERE LPAD(REGEXP_SUBSTR(ESCALATION_LEVEL, '(\d+)$', 1, 1, NULL, 1), 10, '0') 
      <= LPAD(TO_CHAR(输入参数), 10, '0')

说明：LPAD函数将字符串统一补零到10位长度（可根据业务最大级别调整长度），此时'1'转为'0000000001'、'10'转为'0000000010'，字符串比较顺序和数值大小顺序完全一致，符合筛选需求。

优化后完整WHERE子句示例 #

以安全转数字方案为例，你原语句的WHERE条件修改为：

---------------------WHERE CLAUSE MENTIONED IN THE MAIL--------------------------------------------                 
WHERE TO_NUMBER(
    REGEXP_SUBSTR(ESCALATION_LEVEL, '(\d+)$', 1, 1, NULL, 1) 
    DEFAULT -1 ON CONVERSION ERROR
  ) <= 10

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