有没有新手朋友跟小编一样，在 Oracle 数据库里把字符转数字时，突然弹出 “ORA-01722: 无效数字” 的错误？明明看着字符里都是数字，怎么就 “无效” 了呢？急着解决问题，却不知道从哪儿下手，只能对着屏幕发呆。今天就来说说，遇到这种错误该怎么一步步排查，怎么解决，就算是刚接触 Oracle 的新手，看完也能上手操作。
首先得明白，“无效数字” 错误，说白了就是 Oracle 数据库在把字符转成数字的时候，发现这字符 “不像数字”，没法完成转换。那为啥看着像数字的字符，数据库会觉得不像呢？咱们一点点揪原因。
第一步：先看看字符里藏没藏 “非数字” 的东西
很多时候，错误就出在字符里混了咱们没注意到的非数字内容。
比如这些情况：
- 字符里有字母，像 “123a45”，里面的 “a” 就会让转换失败。
- 带了特殊符号，比如 “12.34.56”（多个小数点）、“78,90,12”（多个逗号），数据库分不清哪个是分隔符。
- 藏了空格，尤其是首尾的空格，比如 “123”“456 ”，看着像数字，其实带了空格，数据库也不认。
怎么排查呢？可以用 Oracle 的正则表达式查一查。比如有个字段叫 str_num，想找出里面带非数字的记录，就用这个语句：
select * from 表名 where not regexp_like (str_num, ‘^[0-9.]+$’);
这个语句能筛出所有包含数字和小数点以外字符的记录，一目了然。小编上次就是用这招，发现有个字符里藏了个 “－”（全角减号），换成半角 “-” 就好了。
解决办法也简单：用 replace 函数把非数字的内容去掉。比如去掉字母 “a”：replace (str_num, ‘a’, ”)；去掉首尾空格：trim (str_num)。处理干净再转换，大概率就没问题了。
第二步：检查转换函数的格式掩码对不对
用 to_number 函数的时候，要是字符带了千分符、货币符号这些，没加对格式掩码，也会报错。
举个例子，字符是 “1,234.56”，直接用 to_number (str_num) 转就会报错，因为数据库不知道逗号是千分符。这时候得加格式掩码：to_number (str_num, ‘9,999.99’)，告诉数据库 “逗号是千分符，小数点是小数分隔符”。
但要是格式掩码和字符对不上，比如字符是 “123.45”，却用了 ‘999,99’ 的掩码（逗号和小数点弄反了），照样报错。这时候要么改掩码，要么先把字符里的逗号去掉，比如用 replace (str_num, ‘,’, ”)，再转成数字。
有新手可能会问：“我怎么知道该用啥格式掩码啊？” 其实很简单，字符里有啥特殊符号，掩码里就对应写上啥，数字的地方用 “9” 代替就行。比如 “”，再用 ‘999.99’ 掩码，或者直接用 to_number (replace (str_num, ‘$’, ”), ‘999.99’)。
第三步：看看是不是空值或 “看起来像空” 的字符在捣乱
空值（null）或者全是空格的字符，转数字的时候必报错。比如字段里存的是 ”（空字符串），或者 ‘ ‘（三个空格），用 to_number 转的时候，数据库会直接告诉你 “无效数字”。
怎么排查呢？查一下哪些记录是空值或空格：
select * from 表名 where str_num is null or trim (str_num) = ”;
这些记录就是 “捣蛋鬼”。
解决办法也 straightforward：用 nvl 函数给个默认值。比如 nvl (trim (str_num), ‘0’)，先把空值或空格换成 “0”，再转换：to_number (nvl (trim (str_num), ‘0’))。小编处理报表数据时，每次转数字前都会加这一步，很少再报错了。
表格对比：常见错误原因和解决办法
| 错误原因 | 例子 | 排查语句 | 解决办法 |
|---|---|---|---|
| 含非数字字符 | “12a34”“56.78.90” | regexp_like 排查非数字 | replace 去掉非数字 |
| 格式掩码不匹配 | “1,234” 用 ‘9999’ 转 | 检查字符和掩码的符号是否一致 | 调整掩码或去掉字符中的符号 |
| 空值或空格 | null、“ ” | 查 is null 或 trim 后为空的记录 | nvl+trim 替换成默认值 |
后半段自问自答：这些情况你碰到过吗？
问：字符里只有数字和小数点，为啥还是报错？
答：可能是小数点的位置不对，比如 “123.”（末尾只有小数点），或者 “.456”（开头只有小数点）。这时候可以用 trim 去掉小数点，或者补个 0，比如 replace (str_num, ‘.’, ”) 转成整数，再处理。
问：隐式转换会导致这个错误吗？
答：会！比如用字符字段和数字字段做比较（where str_num = 123），Oracle 会自动把 str_num 转成数字，这时候要是 str_num 里有非数字，就会报错。所以尽量用显式转换，比如 where to_number (str_num) = 123，还能提前发现问题。
问：批量转换时，怎么快速定位错误记录？
答：可以写个循环，逐条转换，捕获错误。比如用 pl/sql 的 for 循环，遍历每条记录，用 exception 捕获 “无效数字” 错误，输出错误的 ID，这样就能精准找到问题记录了。
小编觉得，遇到 “无效数字” 错误，千万别慌。按 “查非数字→查格式→查空值” 的步骤来，基本都能找到原因。新手最容易犯的错，就是没提前检查数据，上来就直接转。其实花几分钟先排查一下字符内容，能省掉后面很多麻烦。转换前多测试几条数据，确定没问题了再批量操作，这招很管用，小编一直这么做。
