Re: how to convert a string into a number?

From: Phillip Jones <phil_at_phillip.im>
Date: Fri, 5 Oct 2012 10:43:28 +0100
Message-ID: <CAOyzJuc2BV-bqfq+D8snphVXUVXRL_CLpyv8D_1zuU1bBreCww_at_mail.gmail.com>



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 - 11:43:28 CEST

Original text of this message