上个星期接了个有关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.   双字节的字符也包含中文符号，日文等等。