Wednesday, April 23, 2008

is_ascii Oracle function


Here is an Oracle function that tells whether a column is all ASCII (from Mark Stafford):

create or replace function is_ascii (v varchar2) return varchar2 as
r boolean := false;
begin
r := length(v)
- length(
translate(v
, chr(1)
|| translate(v, chr(1) || ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~', chr(1) )
, chr(1)
)
) = 0;
if v is null then r := true; end if;
if r = true then return 'y'; else return 'n'; end if;
exception
when others then
return null;
end;
/

No comments: