The Winner is ...

From: Billy <123456781_at_ix.netcom.com>
Date: 1998/01/20
Message-ID: <34C4FF20.EB137ACE@ix.netcom.com>#1/1

Hi again! Thanks to everyone who tried to help. The Grand Winner is agaldo_at_repsol.es for the most elegant solution. The other solutions using Replace and Translate will work but I'm concerned that they would miss unprintable characters (I have not verified this); however, the agaldo_at_repsol.es solution cannot fail under any circumstances.

I fixed and formatted a couple things...here is the final solution:

CREATE OR REPLACE Procedure CONVERT_TO_NUMBER

   ( as_phone IN VARCHAR2,
     an_phone OUT NUMBER )
IS
target_string VARCHAR2(25) := NULL;

len               NUMBER;
num_0         NUMBER;
num_9         NUMBER;
num_c         NUMBER;

BEGIN
 num_0:=ASCII('0');
 num_9:=ASCII('9');
 len:=LENGTH(as_phone);
 FOR counter IN 1 .. len
 LOOP
   num_c:=ASCII(SUBSTR(as_phone,counter,1)); -- use AND not OR

   IF num_c >=num_0 AND num_c<=num_9

      THEN
        target_string:=target_string || CHR(num_c);

   END IF;
 END LOOP;
an_phone := TO_NUMBER(target_string);
END; agaldo_at_repsol.es wrote:

> In article <34C427A2.8A0DFA96_at_ix.netcom.com>,
> Billy <123456781_at_ix.netcom.com> wrote:
>
> > I need to remove all characters from a string like (888)555-1212 and
> > just leave the numbers 8885551212. Some strings could have + signs or
> > other stuff like @, klondike 999-1111. I just want to remove any
> > non-numeric characters.
>
> A simple way to do it is to scan your string and for each character see
> if it is between ASCII(0) and ASCII(9). If it is not, just ignore it, if
> it is then add it to your target string.
>
> this should work:
>
> string VARCHAR2(nn);
> target_string VARCHAR2(nn);
> len number;
> num_0 number;
> num_9 number;
> num_c number;
> begin
> num_0:=ascii('0');
> num_9:=ascii('9');
> len:=lenght(string);
> for counter in 1 .. len
> loop
> num_c=ascii(substr(string,counter,1));
> if num_cr >=num_0 or num_c<=num_9
> then
> target_string:=target_string || chr(num_c);
> end if;
> end loop;
> end;
>
> Hope it helps,
>
> Antonio Galdo
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Tue Jan 20 1998 - 00:00:00 CST