编写函数,将第一步查询出的编码前缀加入到函数中:
create or replace function FUN_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;