Re: check table for "special" characters.

From: Marek Babski <user72_at_go2.pl>
Date: 17 Mar 2004 03:57:35 -0800
Message-ID: <11d263ba.0403170357.3332ad5e@posting.google.com>

> I want to check 12 columns in a table for any characters others than A-Z & 0-9
> (e.g. a-z,?,#,...)
> (Oracle 8)
> How to do?
> Thank you.

You can declare function for checking string for special characters and use this function in query egz.:
 select ename from emp where AZ(ename)>0;

Where AZ is function like this :

create or replace function AZ (S IN varchar2) RETURN integer IS

   maxl number;
   pos integer;
   fail integer;
   c varchar2(1);
BEGIN
   maxl:=length(S);
   fail:= 0;
   for pos in 1..maxl
   loop

        c:=SUBSTR(S,pos,1);
        if instr('ABCDEFGHIJKLMNOPQRSTUWZXV0123456789',c) <= 0 then
          fail:=pos;
          exit;
        end if;

   end loop;
   return(fail);
END AZ; Although I have checked it on Oracle 9 but should work also in 8. Received on Wed Mar 17 2004 - 05:57:35 CST