RE: how to convert a string into a number?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 6 Oct 2012 18:08:47 -0400
Message-ID: <00fd01cda40f$29e8a6b0$7db9f410$_at_rsiz.com>

+1. This is the superior way to have Oracle use all its valid interpretation of numeric strings (and they probably do it faster than you do).

It is somewhat sad that Oracle does not offer a builtin, say, num_if, that returns null for error and the value otherwise. After a version of vsize that understood a null value actually takes up a finite amount of space and should return 0. Of course my suggestion for that is "realsize" abbreviated rsiz. Sure, you can implement this with nvl as a wrapper, but if Oracle did it under the covers it would be faster.

Still, if you store Martin's function spec or something like it in the database if should do pretty well.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Friday, October 05, 2012 2:45 PM
To: phil_at_phillip.im
Cc: jose.soares_at_sferacarta.com; ORACLE-L Subject: Re: how to convert a string into a number?

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


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

Received on Sun Oct 07 2012 - 00:08:47 CEST