Re: Convert char to number ?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 24 Jan 2001 17:14:53 +0100
Message-ID: <94mv3k$tc5$1@s1.read.news.oleane.net>

You can use a function:

create or replace function my_number (myval mytable.markcol1%type) return number
is
begin

return to_number(myval);
exception

when others then

return 0;
end;
/

select sum(my_number(markcol1)) from mytable /

--
Have a nice day
Michel


<tjmxyz_at_my-deja.com> a écrit dans le 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 Wed Jan 24 2001 - 10:14:53 CST