ORA-01722错误排查：数字列与含非数值字符列匹配报错

错误原因 #

执行id = name时，Oracle会隐式将varchar类型的name转换为number类型。如果name中存在无法转换为数值的内容，就会触发ORA-01722无效数字错误。

解决方案 #

方法一：使用VALIDATE_CONVERSION（Oracle 12c+） #

该函数可检查字符串能否转换为指定类型，返回1表示可以转换，0则不行。先过滤可转换的记录再匹配：

SELECT *
FROM company
WHERE VALIDATE_CONVERSION(name AS NUMBER) = 1
  AND id = TO_NUMBER(name);

方法二：正则表达式过滤（兼容低版本） #

通过正则匹配纯数字格式的name，再转换后比较：

SELECT *
FROM company
WHERE REGEXP_LIKE(name, '^[[:digit:]]+$')
  AND id = TO_NUMBER(name);

注：如果name包含带小数的数值，正则需调整为'^[[:digit:]]+(\.[[:digit:]]+)?$'

方法三：自定义安全转换函数（复杂场景） #

针对带特殊格式（如千分位）的数值，可创建自定义函数捕获转换异常：

CREATE OR REPLACE FUNCTION safe_to_num(p_str VARCHAR2) RETURN NUMBER IS
BEGIN
  RETURN TO_NUMBER(p_str);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;
END;
/

使用函数查询：

SELECT *
FROM company
WHERE id = safe_to_num(name);

函数遇到非数值内容会返回null，不会触发错误，且null与任何值比较结果为false，自动过滤无效记录。

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