Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help: Replace non-number to number
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;
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;
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
![]() |
![]() |