how to convert a string into a number?

From: jose soares <jose.soares_at_sferacarta.com>
Date: Fri, 05 Oct 2012 11:31:42 +0200
Message-ID: <506EA8FE.4080205_at_sferacarta.com>

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 when I try
select to_number(text) from test;
I get ORA-01722 invalid number
because ABC can't be converted.

Is there any ideas how to skip not numeric values?

j

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 05 2012 - 11:31:42 CEST