Re: numeric check

Arto Viitanen (arto.viitanen_at_csc.fi) wrote:
: mr. ambastha wrote:
: > i just have a doubt. till date i was writing a pl sql function for
: > checking whether the entered number is numeric or not. can anyone tell
: > whether there is any built in function to check this? Earlier i was
: > using isdigit() and isnumeric() in other languages. is this kind of
: > functions available here?
: > thanks in advance,
: > regards,
: > -ambastha
: >

: Newer version (10g ??) of Oracle has regular expression functions.
: So you could use following in SQL*Plus:

: SQL> variable g_n varchar2(100)
: SQL> set serveroutput on
: SQL> begin
: :g_n := '1222';
: IF :g_n = regexp_substr(:g_n, '^[[:digit:]]+$') THEN
: dbms_output.put_line('OK');

Regular expressions, very powerful, everyone should own one, but they are also tricky, and easy to get wrong.

For example the above doesn't accomodate decimal points, and if it did it probably wouldn't accomodate the language settings for them, not to mention any other allowed representations of numbers. Not to say you shouldn't use RE's, often you should, just realize that they can be seductively simple but end up being a lot more work than you anticipated.

In this case I would go with to_number

• untested function is_a_number( s varchar2) return number is begin return to_number(s); exception
  • This should check just the expected error
  • but I haven't bothered to look it up. when others then return NULL; end is_a_number;

$0.10 Received on Mon Mar 27 2006 - 11:03:24 CST