寻找"筛选中文汉字"的sql

上个星期接了个有关member数据质量处理的小项目，要求是根据会员的联系人名字，公司名，联系电话等信息，从DW中筛选出合法的会员来。

 

对于联系人名字筛选，要求是：

只能是包含1-4个中文汉字，名字可以有空格。不能包括字母数字已经标点符号等。

 

SQL> select * from v$nls_parameters where PARAMETER='NLS_CHARACTERSET';

 

PARAMETER                                                        VALUE

----------------------------------------------------------------

NLS_CHARACTERSET                                                 US7ASCII

 

 

解决方案：

 

中文汉字是双字节存储的，而字母数字以及标点符号等都是单字节的，所以，通过判断联系人里面每个字符是单字节还是多字节的，就知道它是否符合条件了。

 

同事已经写了一个统计有多少个汉字的函数，那么我写sql就方便多了。

 

create or replace function fun_get_chinese_c_cnt

(

 p_string          IN VARCHAR2

)

RETURN NUMBER IS

/*********************************************************************

*模块：

*频率：

*功能：判断字符串中汉字的个数

*作者：wzy

*时间：2009-03-24

*备注：

*********************************************************************/

     l_result               NUMBER;

     l_us7            NUMBER(3);      --US7ASCII编码下字符串长度

     l_str_us7          VARCHAR2(256) := p_string;  --ZHS16GBK编码下的keyword

     l_analyze_code1    VARCHAR2(3);    --双字节字符的第一字节的10进制编码

     i                INT;            --循环控制

BEGIN

     l_us7 := LENGTH(p_string);

     l_result:=0;

     i:=1;

     ------逐个字符分析KeyWord------

     WHILE i <= l_us7 LOOP

           ------获取字符的十进制编码------

           l_analyze_code1:=ascii(substr(l_str_us7,i,1));

           ------字符类型判断------

           IF(l_analyze_code1<128)THEN --单字节的情形

              i:= i+1;

           ELSE--双字节的情形

              i:=i+2;

              l_result:=l_result+1;

           END IF;

           --l_result:=l_result+1;

     END LOOP;--keyword分析完毕

     RETURN(l_result);

END ;

 

那么我现在只需要用 汉字的个数*2 = 字段值的长度 这个条件做筛选就OK了,再考虑空格情况，这样就需要用这个条件之前把空格替换掉就行了，sql如下：

SELECT *

FROM cntmp.membertocompanytemp

WHERE --check first_name

(

            fun_get_chinese_c_cnt(REPLACE(TRIM(first_name), ' ', '')) * 2 = length(REPLACE(TRIM(first_name), ' ', ''))

            AND length(REPLACE(TRIM(first_name), ' ', '')) >= 2 --1个中文字

            AND length(REPLACE(TRIM(first_name), ' ', '')) <= 8   --4个中文字 

)

 

 

总 结：

1.   每个中文汉字占用两个字节，用dump()函数可以看到具体的ascii的值

2.   一般的而言，简体字的编码都大于128，而繁体字的第二个字节编码值小于128，所以如下语句都会查询到结果：

SELECT job_title, DUMP(job_title)

FROM cntmp.membertocompanytemp

WHERE regexp_like(job_title, '[a-zA-Z0-9]')

--job_title LIKE '%a%'

AND fun_get_chinese_c_cnt(REPLACE(TRIM(job_title), ' ', '')) * 2 = length(REPLACE(TRIM(job_title), ' ', ''));

我们刚好可以利用这一点筛选出包含繁体字的数据。

当然，也有极少数繁体字两个字节的编码都大于128的。

3.   双字节的字符也包含中文符号，日文等等。