Re: Replace non-number to number

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

<fransh_at_hotmail.com> a écrit dans le message de news: 1142583303.457864.301350_at_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

It does not seem to me this is what it is requested. I think the OP search for something to remove the non-numeric characters in a value and not to check if the value is numeric or not.

Regards
Michel Cadot Received on Fri Mar 17 2006 - 10:07:06 CST