Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 15th, 2003, 11:54 AM
Registered User
 
Join Date: Sep 2003
Location: Fredericksburg, VA, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Numeric Check in Query

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.
 
Old September 15th, 2003, 03:59 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi jepley,

How about the following :-

c5489@PLAY817.WORLD> select * from t ;

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

c5489@PLAY817.WORLD> select sum(translate(x, '0123456789abcdefghijklmnopqrstuvwxyz','0123456789 '))
"sum (services)" from t ;

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

c5489@PLAY817.WORLD>


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

Cheers,
Prat






Similar Threads
Thread Thread Starter Forum Replies Last Post
From numeric to alfa numeric ebekir XSLT 1 August 10th, 2007 06:13 AM
How to check whether query retuns some rows or not kumar_raj13 ASP.NET 1.0 and 1.1 Professional 1 March 13th, 2006 12:59 AM
Check if query returns results marcin2k Access VBA 10 December 9th, 2005 02:05 PM
check the result of sql query Abhinav_jain_mca ADO.NET 2 August 11th, 2004 11:58 AM
check numeric Tangerine Classic ASP Components 3 March 3rd, 2004 07:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.