Re: Replace non-number to number

From: <fransh_at_hotmail.com>
Date: 17 Mar 2006 00:15:03 -0800
Message-ID: <1142583303.457864.301350@v46g2000cwv.googlegroups.com>

Michel Cadot schreef:

> "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

Or make your own function:
CREATE OR REPLACE
FUNCTION is_number (p_char IN VARCHAR2)

   RETURN BOOLEAN
IS

   num NUMBER;
BEGIN
   num := TO_NUMBER (p_char);
   RETURN TRUE;
EXCEPTION
   WHEN OTHERS
   THEN
       RETURN FALSE;
END is_number;
/

hth,
Frans Hovenkamp Received on Fri Mar 17 2006 - 02:15:03 CST