Oracle中TO_NUMBER转换失败会直接报错，不能靠返回NULL兜底；必须先用REGEXP_LIKE验证格式再转换，或使用12cR2+的DEFAULT ON CONVERSION ERROR语法，并注意NLS设置影响。
Oracle中TO_NUMBER转换失败会直接报错，不能靠返回NULL兜底
oracle的to_number函数在遇到无法解析的字符串（比如'abc'、'12.34.5'、空格开头的' 42'）时，会抛出ora-01722: invalid number错误，而不是像某些数据库那样返回null或默认值。这意味着你不能在select里裸写to_number(col)去处理脏数据——只要一行出错，整条sql就中断。
常见错误现象：ORA-01722在批量处理导入数据、日志字段解析等场景高频出现。
- 必须先验证格式再转换，不能“先转再判”
-
TO_NUMBER本身不支持DEFAULT ... ON CONVERSION ERROR（这是12c+才有的TO_NUMBER扩展语法，但需显式启用且兼容性有限） - 即使加了
WHERE过滤，若谓词未下推或优化器重排执行顺序，仍可能提前触发转换异常
用REGEXP_LIKE预筛合法数字字符串，但注意正则边界和小数点处理
REGEXP_LIKE(col, '^[+-]?[0-9]+(\.[0-9]+)?$')看似能匹配带符号和小数的数字，但实际漏掉多种合法情况：科学计数法（'1e5'）、前导零（'007'）、纯小数（'.5'）、负零（'-0'），而且Oracle对^/$在多行字符串中的行为不稳定。
更稳妥的写法是聚焦业务可接受的子集，例如只允许常规十进制数字：
REGEXP_LIKE(col, '^[[:space:]]*[+-]?([0-9]+\.?[0-9]*|\.[0-9]+)[[:space:]]*$')
说明：
-
[[:space:]]*兼容前后空白（TO_NUMBER本身会自动trim，但正则提前清理更可控） -
[+-]?允许可选符号，但排除'++1'这类非法组合 -
([0-9]+\.?[0-9]*|\.[0-9]+)覆盖'123'、'123.'、'123.45'、'.45'，但排除'.'或'123..45' - 避免用
\d——Oracle正则中\d不一定等价于[0-9]，尤其在不同字符集下
组合REGEXP_LIKE + TO_NUMBER做安全转换，别忘了NVL或CASE
验证通过后才能调用TO_NUMBER，否则仍可能因NLS设置（如小数点/千分位符）出错。推荐结构：
SELECT
CASE
WHEN REGEXP_LIKE(str_col, '^[[:space:]]*[+-]?([0-9]+\.?[0-9]*|\.[0-9]+)[[:space:]]*$')
THEN TO_NUMBER(TRIM(str_col))
ELSE NULL
END AS num_val
FROM your_table;
关键点：
- 必须用
TRIM()再传给TO_NUMBER，防止正则放过' 123 '但TO_NUMBER在某些NLS下报错 - 不要用
NVL(TO_NUMBER(...), 0)——NVL的两个参数会同时求值，异常仍在TO_NUMBER里爆发 - 如果字段可能含千分位符（如
'1,234.56'），得先REPLACE(str_col, ',', '')，但要确认业务上逗号是否一定为分隔符 - 性能提示：
REGEXP_LIKE比普通LIKE慢，大数据量时考虑建函数索引：CREATE INDEX idx_num_check ON your_table (CASE WHEN REGEXP_LIKE(str_col, '...') THEN 1 END);
真正需要容错时，优先用12c+的TO_NUMBER(... DEFAULT ... ON CONVERSION ERROR)
如果你用的是Oracle 12cR2或更高版本，原生TO_NUMBER已支持错误兜底，比正则+CASE更简洁可靠：
TO_NUMBER(str_col DEFAULT -1 ON CONVERSION ERROR)
但要注意：
- 该语法要求数据库兼容级别≥12.2，且
COMPATIBLE参数不能低于'12.2.0' - 默认值只能是常量（不能是列、表达式或
NULL字面量），所以想返回NULL得写DEFAULT NULL ON CONVERSION ERROR - 它仍受NLS影响——比如当前会话
NLS_NUMERIC_CHARACTERS设为',.'，而字符串用'1.234,56'，此时即使加DEFAULT也会报错，因为格式本身违反NLS规则 - 不是所有驱动都支持该语法（老版JDBC thin driver可能报错），上线前务必在真实环境验证
正则验证和DEFAULT ON CONVERSION ERROR不是非此即彼——前者可控性强，后者写法简，但后者掩盖了底层NLS依赖问题。实际项目里，先统一ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';，再选一种方式，比混合使用更稳。
