oracle 过滤字段中的中文，不再洋不洋土不土

他们叫我技术总监

已于 2023-08-17 08:42:15 修改

阅读量2.9k

点赞数 4

分类专栏： Oracle 数据治理文章标签： oracle 数据库数据处理大数据数据分析

于 2023-06-28 16:31:03 首次发布

本文链接：https://blog.csdn.net/qq_29061315/article/details/131422690

版权

Oracle 同时被 2 个专栏收录

27 篇文章 12 订阅

订阅专栏

数据治理

4 篇文章 7 订阅

订阅专栏

前言：

随着数字化建设的不断深入，企业越来越注重，企业数据治理，通过数据消费来辅助决策。作为全球新能源行业综合服务商。我们企业也遇到了很多特殊的业务。当然每个企业都会遇到业务数字化后，总会留些口子来处理一些“费力不讨好的业务”。即业务量很小，标准化管控不值当。当然这些业务也会被完整纳入线上化，麻雀虽小，五脏俱全。这就导致了，在综合业务分析时发现，一些很讨厌的“异常数据”如批次中带有中文之类的。今天和大家来分享，怎么处理这些中文+英文的“洋土批次”。总结在任何企业都适用的中文处理。

一、知己知彼

中文为啥会存在？

1.1业务场景

如上图所示，有些自定义的业务批次的批号不是系统自动生成的，而是通过手工录入系统中，虽然有说一定的制度约束，但是为了方便查看对应含义，不可避免加入了汉字。在网上查看了很多关于过滤中文的SQ说实话，差点意思，基本都不能满足需求，因此想系统整理来避免浪费大家的时间，也可以在自己需要时随时查看~

1.2错误案例

错误示例

select *
from table_name
where regexp_like(text_field, '[\u4E00-\u9FA5]');

错误诠释

上面的代码使用regexp_like函数，通过指定字符unicode值范围，从而实现过滤中文字符的功能，其中unicode值按照中文汉字的范围设置，\u4E00 – \u9FA5是中文汉字 unicode编码表中最常用的字符范围，一般情况下即可满足需求。

错误效果示例

如下图所示最后出来的批次和没过滤是一样的。

原因分析

具体原因，可以参考我以前写的文章说明，在oracle我们不能根据unicode范围来过滤中文。

oracle替换字符串中的中文_oracle去除字符串中的汉字_他们叫我技术总监的博客-CSDN博客

二、思路整理

2.1存储长度与字符串长度比较

中文在oracle 数据库存储时，一个中文会占用两个字符以上。具体要看数据库的字符集编码。一般情况下，数据库的NLS_CHARACTERSET 为AL32UTF8或UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个中文占用两个字节。

如上图所示一个中文占用了2个字节。

因此我们就可以使用第一种办法啦，就是通过lengthb('中文')！=length('中文')来找到，包含中文的批号啦。正确示例案例如下图所示

代码：

--查询带中文的数据
 select 生产批次号   from   BI.QZ_zB_GCPJCSJ zb  
          where 1=1 
          and flag='已清洗'
          and LENGTH(生产批次号)!= LENGTHB(生产批次号);--查询中文批次

--查询非中文的数据
 select 生产批次号   from   BI.QZ_zB_GCPJCSJ zb  
          where 1=1 
          and flag='已清洗'
          and LENGTH(生产批次号)= LENGTHB(生产批次号);--查询非中文批次

效果：

诠释：

如上图所示，通过LENGTH(生产批次号)!= LENGTHB(生产批次号)就能找到包含中文的批次了。因此我们如果只想看非中文的批次，我们使用LENGTH(生产批次号)= LENGTHB(生产批次号)就能达到目的啦，是不是非常简单。

三、还有没有其他思路

在上面我们使用中文的存储规则来过滤中文数据，其实我们还可以利用ascii表的规则来查询中文数据。

3.1ascii表查找法

批次规则一般包括数字、字母、特殊符号和汉字，去掉ascii表里面没有的字符不就是中文了。详情可参考对应ascii表。

具体我们将中文替换成一个不会出现的复杂字符串，如“他们叫我技术总监”如果判定替换后的字符串是否包含“他们叫我技术总监”的字符即可。详情看下面案例。

3.2正式案例

代码：

    select 生产批次号
            from BI.QZ_ZB_GCPJCSJ
          
           where instr(regexp_replace(生产批次号,
                                      '[' || chr(128) || '-' || chr(255) || ']',
                                      '他们叫我技术总监'),
                       '他们叫我技术总监',
                       1,
                       1) > 0
--将中文替换为一个不可能出现的字符，然后判断替换的字符串是否包含对应的字符

效果：

诠释：

我们将中文替换成一个不会出现的复杂字符串，然后再判定替换后的字符串是否包含替换后的字符即可。对应替换后的字符串是中文即可。

四、总结

在本次文章中，包含了常用所有的中文处理方案，如查找包含中文的数据、查找非中文的数据，将中文替换成其他字符等。当然我们有时候还需要在一堆包含中文的字符串中获取数字、日期等。如“2.2元/斤”，获取2.2作为单价。如“2023-06-22我们在一起”获取2023-06-22的日期用来分析。对应处理方法我在往前做了文章说明，希望对大家有用，不再去网上浪费时间搜索错误的教程了~

oracle-替换中文、回车、换行、倒序截取等，从OA一堆意见里面找出你想要的审批日期_oracle替换换行符_他们叫我技术总监的博客-CSDN博客

从数据库字符串中获取数字部分，用于数据分析_在字符串123456中截取出2345数据库_他们叫我技术总监的博客-CSDN博客