Re: SQL to remove char from string?

From: <agaldo_at_repsol.es>
Date: 1998/02/16
Message-ID: <885300481.1565261828@dejanews.com>#1/1

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 Mon Feb 16 1998 - 00:00:00 CST