神奇ORA-01722無效的數字

开发反馈，在查询一个表的时候，查询的栏位里面明明没有非数字的字符串，但是在使用TO_NUMBER时，会报错ORA-01722: 無效的數字

开发sql:

SELECT SCR_SPEED
          FROM monkey.monkey_test
         WHERE ADD_DATE >=
                  TO_DATE ('2022/08/01 07:00:00', 'yyyy/MM/dd hh24:mi:ss') and to_number(SCR_SPEED) > 0;     

报错，无效的数字

排查1：

SELECT TO_NUMBER(SCR_SPEED)
          FROM monkey.monkey_test
         WHERE ADD_DATE >=
                  TO_DATE ('2022/08/01 07:00:00', 'yyyy/MM/dd hh24:mi:ss');

不报错，这时候就很奇怪了，明明可以TO_NUMBER，但为啥开发会报错呢

排查2：

WITH tt
     AS (SELECT TO_NUMBER (SCR_SPEED) SCR_SPEED
           FROM MONKEY.monkey_test
          WHERE ADD_DATE >=
                   TO_DATE ('2022/08/01 07:00:00', 'yyyy/MM/dd hh24:mi:ss'))
SELECT *
  FROM tt
 WHERE tt.SCR_SPEED > 0;

还是报错，我把可以跑的部分抽出来，再跑还是不行

排查3：

CREATE TABLE monkey.test
AS
   SELECT TO_NUMBER (SCR_SPEED) SCR_SPEED
     FROM MONKEY.monkey_test
    WHERE ADD_DATE >=
             TO_DATE ('2022/08/01 07:00:00', 'yyyy/MM/dd hh24:mi:ss');

select * from monkey.test where scr_speed >0;

不再报错，说明在这个时间范围里面确实没有非数字的字符串

经过对比，发现to_number只有在where条件里面的时候会报错，此时，猜测作为where条件的时候，需要去整个表里面筛选记录，虽然在时间段内没有非数字的字符串，但是整个表里面有，用如下sql验证

SELECT *  FROM monkey.monkey_test where  length(REGEXP_REPLACE(SCR_SPEED,'[^0-9]','wocao'))>3

发现确实有一笔时开发测试的记录，SCR_SPEED的值为test，通知开发删除后，执行正常。