I have a field that is defined as varchar2(25) but mostly contains numeric values. A report that I am creating needs to ignore records that contain non-numeric values, and then sum the records where the values are numeric. Sample data is like:

district services
-------- ---------
GA 45
HA 13
NN meter
HM 32

The desired output would be:

sum(services)
-------------
90

Any suggestions would be appreciated.

Hi jepley,

How about the following :-

[email protected]D> select * from t ;

X
-------------------------
45
13
meter
32

[email protected]D> select sum(translate(x, '0123456789abcdefghijklmnopqrstuvwxyz','0123456789 '))
"sum (services)" from t ;

sum (services)
--------------
            90

[email protected]D>


Just put in other characters besides a to z to make the query more robust.

Cheers,
Prat