Search This Blog

Friday, July 11, 2014

Function to identify non-english or non-ASCII charaters

CREATE OR REPLACE FUNCTION RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:='^'||TO_CHAR(INPUT_STR)||'^';
cnt:=length(str);
for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :='^'||REPLACE(str, CHR(askey),'');
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),'^'),'^'));
RETURN (OUTPUT_STR);
end;
/