【背景】

进行数据抽取时，抽取“联系电话”字段时，发现存在部分中文、英文，需要对该字段进行清理。

【垃圾数据存在原因】

如果有“联系电话”这样的字段，在设计之初就设置为number，而不是设置成varchar2，再在前端软件做一个约束，相信就不会有这样存在英文或中文的干扰，也就不会出现垃圾数据了。而如今，遇到了，只好想办法处理了。

【解决过程】

开始想复杂了，想要用一个函数做判断，然后再利用case处理，但后来得到其它公司的DBA提示，用了个正则表达式就搞定了，诶呀！

?

下面模拟做个试验：

1、创建实验表（源端）

createtable test_num

(

telvarchar2(32)

);

2、创造些实验数据（源端）

3、创建实验目标表（目标端）

createtable test_num_new

(

telnumber

);

4、由源端向目标端插入数据

5、创建判断函数

create or replace function isnum(v_in varchar2)

return varchar is

val_err exception;

pragma exception_init(val_err,-6502);

scrub_num number;

begin

scrub_num := to_number(v_in);

return 'Y';

exception when val_err then

return 'N';

end;

?

函数作用：传递给isnum函数一个值，如果是number返回Y，如果不是number返回N。

6、使用case，利用函数完成清洗

insert into test_num_new select case isnum(TEL) when 'Y' then TEL

when 'N' then ''

end from test_num;

commit;

7、查看清洗后目标表效果

通过查看，完成了对于垃圾数据的清洗。但这么做是不是有些复杂呢？

换个思路，利用正则表达式。

8、清空目标数据

truncate table test_num_new;

9、利用正则表达式向目标表插入数据

insert into test_num_new select REGEXP_REPLACE(tel,'\D','') from test_num;

commit;

?

10、查看目标表

可以看到利用正则表达式，处理过的数据，去除了中文、英文的干扰，只保存了number，达到预期效果，比之前的case与函数实现上，简化了不少。

实验完毕。

【小结】

利用正则表达式，只保留number去除中文、英文干扰。

语法：REGEXP_REPLACE(字段名或字符串,'\D','')

含义：把字段中非number的字符去掉

其中\D：表示非number字符。