Results 1 to 3 of 3

Thread: Convert

  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Angry Unanswered: Convert

    Hi,

    This proving a bit of a riddle to solve:

    I want to convert a string to a number but to 0 if it is not a valid number.

    e.g.
    to_number('123') => 123
    to_number('F-F') => 0

    I want to keep this in a single sql statement - I want to avoid catching exceptions.

    Help!

    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Convert

    PHP Code:
    create or replace function my_to_numberp_string in varchar2 )
    return 
    number
    is
    begin
      
    return to_number(p_string);
    exception
      when others then 
    return 0;
    end;

    Now use that function in place of TO_NUMBER in your SQL.

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    I interpreted your question as - you have a field that may have numbers or numbers and characters, or characters. You want to return a 0 when there are any characters at all. :

    SELECT case when (instr(TRANSLATE(upper('&YOUR_VARCHAR2'),
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
    '0123456789::::::::::::::::::::::::::'),':')) > 0
    then to_number('0')
    else to_number('&YOUR_VARCHAR2')
    end
    test
    FROM DUAL
    /


    Rem you would have to translate any special characters that you may have in that field, too
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •