去除制表符、空格、换行符、空格符特殊 符号

由于业务需要，不定期的会导入一部分员工的信息，大概有2万多的数据量，为满足速度，采用sqlload的方式，这些数据无法保证数据是否会存在手工误输一些特殊符号，
所以要进行处理，这里我们导入时，借助一张临时表 member_load_imp。
这里有几种方式
常见特殊字符：
chr(9) –制表符
chr(10) –换行符
chr(13) –回车符
chr(32) –空格符
chr(34) –双引号“””
WITH T1 AS
(SELECT ‘11 ’ || CHR(9) || CHR(10) || CHR(13) || ’ 12321’ ID FROM DUAL)
select id,
regexp_replace(id,
chr(32) || ‘|’ || chr(9) || ‘|’ || chr(10) || ‘|’ ||
chr(13))
from t1;

①：使用for+replace的方式完成 (replace每次只能处理一个字符)
set serveroutput on size 200000;
declare
l_sql varchar2(1000);
begin
for i in (select column_name
from ALL_tab_columns
where table_name = ‘MEMBER_LOAD_IMP’) loop
l_sql := ‘update member_load_imp set ’ || i.column_name ||
‘=replace(replace(replace(replace(’ || i.column_name ||
‘, CHR(9), ””), chr(10), ””),
chr(13),
””),
chr(32),
””)’;
dbms_output.put_line(l_sql);
end loop;
end;
/
②：使用游标+正则表达式
declare
l_name varchar2(200);
l_sql varchar2(2000);
l_cnt number;
cursor l_cur is select column_name
from ALL_tab_columns
where table_name = ‘MEMBER_LOAD_IMP’ ;
begin
open l_cur;
loop
fetch l_cur into l_name;
l_sql := ‘update member_load_imp set ‘||l_name||’= regexp_replace(‘||l_name||’,
chr(32) || ”|” || chr(9) || ”|” || chr(10) || ”|” ||
chr(13))’;
execute immediate l_sql;
l_cnt :=sql%rowcount;
commit;
–dbms_output.put_line(l_sql);
dbms_output.put_line(l_cnt);
exit when l_cur%notfound;
end loop;
close l_cur;
end;
/

测试：
SQL> declare
2 l_name varchar2(200);
3 l_sql varchar2(2000);
4 l_cnt number;
5 cursor l_cur is select column_name
6 from ALL_tab_columns
7 where table_name = ‘MEMBER_LOAD_IMP’ ;
8 begin
9 open l_cur;
10 loop
11 fetch l_cur into l_name;
12 l_sql := ‘update sunif.member_load_imp set ‘||l_name||’= regexp_replace(‘||l_name||’,
13 chr(32) || ”|” || chr(9) || ”|” || chr(10) || ”|” ||
14 chr(13))’;
15 execute immediate l_sql;
16 l_cnt :=sql%rowcount;
17 commit;
18 –dbms_output.put_line(l_sql);
19 dbms_output.put_line(l_cnt);
20 exit when l_cur%notfound;
21 end loop;
22 close l_cur;
23 end;
24 /
5
5
5
5
5
5
5
5
5
5
5
5
PL/SQL procedure successfully completed