ORA-01722 无效数字的处理

1.问题描述   

    今天处理一个表，记录数2千多万，总是报错‘ORA-01722 无效数字’，原始查询就不贴了，以下面这个为例：

SELECT SFZH,LENGTH(SFZH)，TO_CHAR(TO_NUMBER(SUBSTR(SFZH,7,4))+2)  FROM V_RY_XX

WHERE LENGTH(SFZH)>18 AND SFZH is not null

ORDER BY LENGTH(SFZH) DESC;

2.原因

    SFZH有非数字的数据，所以在TO_NUMBER(SUBSTR(SFZH,7,4))时会产生将非数字的数据转换为数字，所以报错ORA-01722 。

3.解决办法 

在where条件中增加REGEXP_INSTR(SFZH,'[^[:digit:]]{1,}',1)=0，即仅保留SFZH是数字的记录。

    SELECT SFZH,LENGTH(SFZH)，TO_CHAR(TO_NUMBER(SUBSTR(SFZH,7,4))+2)  FROM V_RY_XX

WHERE LENGTH(SFZH)>18 AND SFZH is not null  and REGEXP_INSTR(SFZH,'[^[:digit:]]{1,}',1)=0
ORDER BY LENGTH(SFZH) DESC;