> On Fri, 17 Mar 2006 02:55:28 +0000, joebayer 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.
>
The easiest way is:
select replace(translate(lower(colA),
'abcdefghijklmnopqrstuvwxyz?<>,!@#$%^&*()=-:;"|[]{}/?~''',
'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'),'z',null)
from your_table
Received on Fri Mar 17 2006 - 10:48:39 CST
