Re: Convert char to number ?

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Tue, 30 Jan 2001 12:51:46 -0800
Message-ID: <9579i2$6b9$1@spiney.sierra.com>

You will need to do this inside a PL/SQL procedure and deal with the "bad" numbers
by using

EXCEPTION
WHEN INVALID_NUMBER THEN
some code

<tjmxyz_at_my-deja.com> wrote in message news:94msm7$7ds$1_at_nnrp1.deja.com...
> 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/
Received on Tue Jan 30 2001 - 14:51:46 CST