I have the following code that returns an error message if my value is invalid. I would like to give the same error 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 simply used ISNUMERIC()
. I would like to do something similar in Oracle. Such as,
IF((!ISNUMERIC(value)) or (value<10000) or (value>7200000))
THEN ...
REGEXP_LIKE(column, '^[[:digit:]]+$')
returns TRUE if column holds only numeric characters
From Oracle DB 12c Release 2
you could use VALIDATE_CONVERSION function:
VALIDATE_CONVERSION determines whether expr can be converted to the specified data type. If expr can be successfully converted, then this function returns 1; otherwise, this function returns 0. If expr evaluates to null, then this function returns 1. If an error occurs while evaluating expr, then this function returns the error.
IF (VALIDATE_CONVERSION(value AS NUMBER) = 1) THEN
...
END IF;
db<>fiddle demo
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 )
If you happen to be using Oracle 12.2 or later, there are enhancements to the to_number
function that you could leverage
IF( to_number( str default null on conversion error ) >= 1000 AND
to_number( str default null on conversion error ) <= 7000 )
You can use the following regular expression which will match integers (e.g., 123
), floating-point numbers (12.3
), and numbers with exponents (1.2e3
):
^-?\d*\.?\d+([eE]-?\d+)?$
If you want to accept +
signs as well as -
signs (as Oracle does with TO_NUMBER()
), you can change each occurrence of -
above to [+-]
. So you might rewrite your block of code above as follows:
IF (option_id = 0021) THEN
IF NOT REGEXP_LIKE(value, '^[+-]?\d*\.?\d+([eE][+-]?\d+)?$') OR TO_NUMBER(value) < 10000 OR TO_NUMBER(value) > 7200000 THEN
ip_msg(6214,option_name);
RETURN;
END IF;
END IF;
I am not altogether certain that would handle all values so you may want to add an EXCEPTION
block or write a custom to_number()
function as @JustinCave suggests.
The best answer I found on internet:
SELECT case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null
then 'numeric'
else 'alpha'
end
FROM tab1;