using to_number on varchar field Oracle 9i

Posted on 2005-05-05
Medium Priority
Last Modified: 2008-02-01
IS there anyway to prevent an error when a fields value is not numeric. I have a column of varchars - some with numeric values and some not. I'm only interested in returning the fields that have valid numbers (converted by to_number). I am happy to have zero returned for non-numerics or for them to be excluded from the set entirely. At the moment I just get an error when to_number hits the 'abbc'
So for
I want
Question by:colindow
  • 2
  • 2
LVL 22

Accepted Solution

earth man2 earned 500 total points
ID: 13933992
create or replace function number_or_zero( str in varchar2 )  return number as
   return to_number( trim( str ) );
   when others then
   return 0;

SQL> select number_or_zero(' 123 ' ) as go_1, number_or_zero( ' abbc' ) as go_2 from dual;

      GO_1       GO_2
---------- ----------
       123          0


Author Comment

ID: 13934017
Many thanks
Is this more efficient than using a predicate like

WHERE TRIM (TRANSLATE (string1, '.0123456789', ' ') ) IS NULL

which I found on techonthenet ?
LVL 22

Expert Comment

by:earth man2
ID: 13934112
what happens if there are 2 decimal points ?

Author Comment

ID: 13934169
Not sure. My situation doesnt really involve decimal points but I guess a thorough solution would allow for this.
As I am in a position to create the function I'll go with you solution as it seems neater and probably more efficient.

Many thanks for your help

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question