How to check if a value is a number in Oracle 10g

1143020 pts.
Tags:
Oracle 10g
Oracle Query
I'm wondering if there's a way to check if a value, from a column in Oracle 10g query, is actually a number so I can compare it. This is what I have so far:
select case when ( is_number(myTable.id) and (myTable.id >0) ) 
            then 'Is a number greater than 0' 
            else 'it is not a number' 
       end as valuetype  
  from table myTable
Am I on the right track here? Thanks!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no buil-it function for that in Oracle.

You would have to write your own, which could be something like this. If you are going to use it in SQL, it cannot return a boolean type:

function is_number(value in varchar2) return varchar2 is
    nValue number;
Begin
    nValue := to_number(value);
    return 'true';
Exception
    when value_error then
      return 'false';
End;

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: