Re: how to convert a string into a number?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 5 Oct 2012 20:44:57 +0200
Message-ID: <CALH8A90NSPBn6RDqCE77gJm+10Vbby3pD7V80o-xh1a=scskkg_at_mail.gmail.com>

what's about
CREATE OR replace FUNCTION *To_number_only* (in_num *VARCHAR2*) RETURN *NUMBER*
IS
  vosuser user_users.username%*TYPE*;   ret_number *NUMBER*;
  ecode *NUMBER*(38);
BEGIN
    ret_number := *To_number*(in_num);

    RETURN ret_number;
EXCEPTION
  WHEN OTHERS THEN
             ecode := SQLCODE;

             dbms_output.*Put_line*('to_number_only - '
                                  || ecode);

             RETURN NULL;

END to_number_only;
/

WITH test

     AS (SELECT '123' AS t
         FROM   dual
         UNION
         SELECT '3' AS t
         FROM   dual
         UNION
         SELECT '43' AS t
         FROM   dual
         UNION
         SELECT 'ABC' AS t
         FROM   dual)
SELECT t,
       *To_number_only*(t) tno

FROM test;

On Fri, Oct 5, 2012 at 11:43 AM, Phillip Jones <phil_at_phillip.im> wrote:

> You don't state what you would like to happen when the number
> isn't numeric, so I'll just ignore the strings.
> There's a million and one ways to do this, but it's easy with a regexp:
>
> SQL> with test as
> (
> select '123' as t from dual
> union
> select '3' as t from dual
> union
> select '43' as t from dual
> union
> select 'ABC' as t from dual
> )
> select to_number(t)
> from test
> where regexp_like(t,'^[[:digit:]]+$');
>
> TO_NUMBER(T)
> ------------
> 123
> 3
> 43
>
> SQL>
>
> Phil
>
> On Fri, Oct 5, 2012 at 10:31 AM, jose soares <jose.soares_at_sferacarta.com
> >wrote:
>
> > Hi all,
> >
> > I would like to convert
> > values of a varchar2 column type to a number.
> > I'm using to_number function
> > but I have mixed values in my table,
> > there are some columns with not numeric values
> > and there are some others with numbers as in:
> >
> > select * from test;
> >
> > text
> > --------
> > 123
> > 3
> > 43
> > ABC
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 05 2012 - 20:44:57 CEST