Kyle Williamson Kyle Williamson - 4 years ago 336
SQL Question

How to tell if a value is not numeric in Oracle?

I have the following code that returns an error message if it is an invalid value. I would like give the same message if the value given is not numeric.

IF(option_id = 0021) THEN
IF((value<10000) or (value>7200000) or /* Numeric Check */)THEN
ip_msg(6214,option_name); -- Error Message
return;
END IF;
END IF;


In SQL Server, I used
ISNUMERIC()
. I would like to do something similar in Oracle. Such as,
IF((!ISNUMERIC(value)) or (value<10000) or (value>7200000))THEN
...

Answer Source

There is no built-in function. You could write one

CREATE FUNCTION is_numeric( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 1;
EXCEPTION
  WHEN value_error
  THEN
    RETURN 0;
END;

and/or

CREATE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN l_num;
EXCEPTION
  WHEN value_error
  THEN
    RETURN NULL;
END;

You can then do

IF( is_numeric( str ) = 1 AND 
    my_to_number( str ) >= 1000 AND
    my_to_number( str ) <= 7000 )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download