今天有客户反映一个同事开发的应用出现点问题,经测试定位错误,在一次导入数据中某条数据的一个字段值(varchar2),存储的应该为数字类型,但是导入数据中误将'.'写成了'。',导致问题的出现,发现问题后,使用SQL查询是否还存在其他类似错误数据。同事写的是第一种SQL,我记得10g后支持正则表达式的,整理如下:
10g前一般使用的方法是:
SELECT * FROM table_name t WHERE LENGTH(TRIM(TRANSLATE(t.column, ' +-.0123456789', ' '))) > 0
---- 注释:查询 table_name表中column字段不为数字类型的数据
10g及以后的版本支持了正则表达式,方法是:
SELECT * FROM table_name t WHERE REGEXP_INSTR(t.column,'[^+-/.0-9]') > 0
---- 注释:以上REGEXP_INSTR方法返回满足正则表达式(不为+-.0123456789)的第一个字符的位置,没有满足条件的则返回0。
7W条数据测试时间不相上下,因为测试数据中重复比较多,且字符串不长,如果有好的测试数据,对比效果会明显些。