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 ...8 Answers
REGEXP_LIKE(column, '^[[:digit:]]+$')returns TRUE if column holds only numeric characters
6From 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;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 )The best answer I found on internet:
SELECT case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null then 'numeric' else 'alpha' end
FROM tab1;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.
CREATE OR REPLACE FUNCTION IS_NUMERIC(P_INPUT IN VARCHAR2) RETURN INTEGER IS RESULT INTEGER; NUM NUMBER ;
BEGIN NUM:=TO_NUMBER(P_INPUT); RETURN 1;
EXCEPTION WHEN OTHERS THEN RETURN 0;
END IS_NUMERIC;
/This regular expression will match numbers like 5 , -5, +5, 5.44, 3.45e-3
REGEXP_LIKE('54.55e33', '^[+-]?\d+([.]\d+)?(e[+-]?\d+)?$')SELECT DECODE(REGEXP_COUNT(:value,'\d'),LENGTH(:value),'Y','N') AS is_numeric FROM dual;There are many ways but this one works perfect for me.
