Re: how to convert a string into a number?
- From: Phillip Jones <phil@xxxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Oct 2012 10:43:28 +0100
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@xxxxxxxxxxxxxx>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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
