Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Convert char to number ?

Re: Convert char to number ?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Wed, 24 Jan 2001 16:15:00 GMT
Message-ID: <94mv1p$9u5$1@nnrp1.deja.com>

In our last gripping episode tjmxyz_at_my-deja.com wrote:
> Simple question..
>
> I have a column markcol1
> I need to convert it to a number and sum it but I can't guarantee all
> values will be numbers..
>
> For example
> MARKCOL1
> -----------
> abc
> 10
> 0
> .NULL.
> 12
>
> IF I do...
> SELECT SUM(TO_NUMBER(markcol1)) from mytable;
>
> I get...
>
> ORA-01722: invalid number
>
> How do I ignore any character expressions....
>
> Tried also
> SELECT SUM(TO_NUMBER(NVL(markcol1,0))) from mytable;
>
> But of course that did not work...
>
> What do I use?
>
> Sent via Deja.com
> http://www.deja.com/
>

Presuming there are no values similar to the following:

1ab
10e
9c

and the like you can use the following query:

select sum(to_number(nvl(markcol1, 0)))
from mytable
where substr(markcol1, 1, 1) in
('0','1','2','3','4','5','6','7','8','9');

You should compute a valid sum.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/
Received on Wed Jan 24 2001 - 10:15:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US