ORACLE中如何去除不可见字符（非空格、回车等）

1、首先确定出错数据含有的不可见字符。可以使用dump函数查看：SELECT dump('') FROM dual;DUMP('')---------------------Typ=96 Len=2: 253,210

2、编写函数，将第一步查询出的编码前缀加入到函数中：create or repla罕铞泱殳ce function F炽扃仄呦UN_REPLACE_SPACE(p_text varchar2) return varchar2 is v_length int; v_lengthb int; v_dump varchar2(4000); v_substr varchar2(4000); i int; v_result varchar2(4000);begin v_length := 0; v_lengthb := 0; i := 1; v_substr := ''; v_dump := ''; v_result := ''; select length(p_text), lengthb(p_text) into v_length, v_lengthb from dual; while i <= v_length loop v_substr := substr(p_text, i, 1); select dump(v_substr) into v_dump from dual; if v_dump like '%170,___' then v_substr := ''; end if; if v_dump like '%171,___' then v_substr := ''; end if; if v_dump like '%248,___' then v_substr := ''; end if; if v_dump like '%249,___' then v_substr := ''; end if; if v_dump like '%250,___' then v_substr := ''; end if; if v_dump like '%251,___' then v_substr := ''; end if; if v_dump like '%252,___' then v_substr := ''; end if; if v_dump like '%253,___' then v_substr := ''; end if; v_result := v_result || v_substr; i := i + 1; end loop; return v_result;exception when others then return '';end;

3、验证处理后结果：SELEC哌囿亡噱T dump('你好啊'),fun_replace_space('你好啊') FROM dual;DUMP(媪青怍牙'你好啊') FUN_REPLACE_SPACE('你好啊')--------------------------------------------- --------------------------------------------------------------------------------Typ=96 Len=8: 196,227,186,195,253,210,176,161 你好啊