using to_number on varchar field Oracle 9i

Posted on 2005-05-05
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
    LVL 22

    Accepted Solution

    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

    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
    what happens if there are 2 decimal points ?

    Author Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now