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:
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-lReceived on Fri Oct 05 2012 - 11:43:28 CEST