oracle几种查找非数字字符的方法

没什么不可放下

已于 2024-04-17 17:08:44 修改

阅读量1.2k

点赞数 9

分类专栏： oracle 文章标签：数据库 oracle

于 2024-04-17 17:05:55 首次发布

本文链接：https://blog.csdn.net/zhj330491369/article/details/137878777

版权

1 篇文章 0 订阅

订阅专栏

本文介绍了在大数据ETL过程中，如何使用TRANSLATE函数和正则表达式找出VARCHAR字段中因输入错误导致的非数字字符，从而快速定位ORA-01722错误记录，提供了多种实用的SQL模板和实例以供参考。

摘要由CSDN通过智能技术生成

在大数据领域ETL（清洗、加载、转换）中会碰到表中存在varchar类型的字段但是存储的是数字编码，由于手工输入的原因可能会将空格、字母等输入进去，然而下游入库对应的字段又是number类型，这样很容易导致sql报错：ORA-01722: 无效数字，如何快速找到异常记录，下面分享几种常见的方法：

一、TRANSLATE函数，这也是最简单直接的方法：

select * from table_name where TRANSLATE(字段,'0123456789.','') IS NOT NULL;

解释：查找非数字、非小数点的异常记录

演变1：TRANSLATE后配合replace函数：replace(translate(字段, '0123456789', '0'), '0', '') IS NOT NULL---通过replace把结果替换为空，最终筛选出不符合条件的记录

实例：select * from table_name where replace(translate(fee, '0123456789', '0'), '0', '') IS not NULL

演变2：配合nvl trim等函数：(NVL2(TRANSLATE(TRIM(字段), '\1234567890.', '\'),'CHAR', 'NUMBER') = 'CHAR'

基本原理一样，如下提供几种现成的模板，过滤非数字（包括负号和小数点）字符，大家在实际应用中直接替换表和字段即可：

模板1：select * from 表 t where trim(translate(字段,'-.0123456789',' ')) is not null

模板2：select * from 表 t where replace(translate(字段, '-0123456789.', '0'), '0', '') IS not NULL

模板3：select * from 表 t where (NVL2(TRANSLATE(trim(字段), '\-1234567890.', '\'),'CHAR', 'NUMBER')) = 'CHAR';

模板4：select * from 表 t where (NVL2(TRANSLATE(字段, '\-1234567890.', '\'),'CHAR', 'NUMBER')) = 'CHAR';---包括空格都能捞出来

大家可以选择适合自己的模板去使用

二、通过正则表达式查找非数字字符：

select * from table_name where REGEXP_REPLACE(字段, '[0-9]') IS NOT NULL;

解释：以上正则表达式的意思是把0-9数字删除后如果结果还不为空说明含有非数字字符

实例：将含有非数字的部分去掉，保留数字部分：

select regexp_replace('字段', '[^0-9]') from dual;--结果是去掉非数字后的纯数字值

相反，去掉数字部分，保留非数字部分（表达式中去掉特殊符号^ 即可）

关注