Help: Replace non-number to number

From: joebayer <joebayer(nospam)_at_hotmail.com>
Date: Fri, 17 Mar 2006 02:55:28 GMT
Message-ID: <AypSf.14780$o41.11768@trnddc06>

Group,

I would like to replace all the non-number in a column to number. For example:
colA

$356
6,700
Y3900.01
L567
should be
colA

356
6700
3900.01
567

So I created a function
create or replace function test_f (v_1 in varchar2) return varchar2 is

    v_2 varchar2(30) default null;
    v_i integer default 0;
    v_max integer default 0;

begin

    v_max := length(v_1);
    for v_i IN 1..v_max
    loop

        v_2 := v_2||substr(v_1,v_i,1);
    end loop;
    return v_2;
end;

But in this function, I would like to add if substr(v_1, v_i, 1) is not number, then v_2 :=v_2

In Oracle, is there any way to tell where the variable is number or not? Or do you have any other idea how to achive this?

Thanks for your help. Received on Thu Mar 16 2006 - 20:55:28 CST