Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Replace non-number to number

Re: Replace non-number to number

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 17 Mar 2006 06:25:52 +0100
Message-ID: <441a48b7$0$27077$626a54ce@news.free.fr>

"joebayer" <joebayer(nospam)@hotmail.com> a écrit dans le message de news: AypSf.14780$o41.11768_at_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.
|
|

Have a look at the TRANSLATE function.

Regards
Michel Cadot Received on Thu Mar 16 2006 - 23:25:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US