Re: Help: Replace non-number to number

From: Jerome Vitalis <vitalismanREMOVETHAT_at_gmail.com>
Date: Fri, 17 Mar 2006 13:23:20 +0100
Message-ID: <pan.2006.03.17.12.23.12.391000@gmail.com>

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.

Are you using Oracle 10g? In this case you can use a regular expression, for instance:

SQL> select cola from v;

COLA

---

$356
6,700
Y3900.01
L567

SQL> select regexp_replace(cola,'[^0-9\.]','') from v;

REGEXP_REPLACE(COLA,'[^0-9\.]','')

---

356
6700
3900.01
567

Anybody knows why '[^\d\.]' does not work here (instead of '[^0-9\.]')? Maybe something to do with my character set (WE8ISO8859P1).

Jérôme Received on Fri Mar 17 2006 - 06:23:20 CST