Re: Help: Replace non-number to number

From: <fitzjarrell_at_cox.net>
Date: 16 Mar 2006 20:23:46 -0800
Message-ID: <1142569426.456222.234850@i39g2000cwa.googlegroups.com>

joebayer (nospam) wrote:
> 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.

There is no 'isnumber' function in Oracle, however it's fairly easy to modify your function to perform the actions you want (stripping alpha characters from alphanumerics to return only the numeric portion):

create or replace function valid_nbr (p_str in varchar2) return number is

    v_str varchar2(30) default null;
    v_i integer default 0;
    v_max integer default 0;
    v_dec number:=0;

begin

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

        for v_ascii_num in 48..57 loop
                if substr(p_str, v_i, 1) = chr(v_ascii_num) then
                        v_str := v_str||substr(p_str,v_i,1);
                end if;
                if substr(p_str, v_i, 1) =  '.' then
                        v_dec := v_max - v_i;
                end if;
        end loop;

    end loop;

    return case when v_dec = 4 then to_number(v_str)/10000
                when v_dec = 3 then to_number(v_str)/1000
                when v_dec = 2 then to_number(v_str)/100
                when v_dec = 1 then to_number(v_str)/10
                else to_number(v_str) end;

end;
/

This is not the most elegant of solutions (I threw it together in minutes after reading your post) as one could use pl/sql tables or varrays to load all posible decimal versions of the number in question and return them according to the index generated by v_dec, however I'll leave that to you to code. Suffice it to say the above code returns valid numbers, including decimal numbers like your 3900.01 example, up to four decimal places. Should you need more you can modify the case statement or you could implement the pl/sql table/varray solution.

I hope this helps you accomplish your goal.

David Fitzjarrell Received on Thu Mar 16 2006 - 22:23:46 CST