How can you tell if a value is not numeric in Oracle

Question

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 lt 10000  or  value gt 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 lt 10000  or  value gt 7200000       THEN

User · Answer

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 lt  fiddle demo

User · Answer

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    gt   1000 AND     my to number  str    lt   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    gt   1000 AND     to number  str default null on conversion error    lt   7000

User · Answer

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   lt  10000 OR TO NUMBER value   gt  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

User · Answer

The best answer I found on internet   SELECT case when trim TRANSLATE col1   0123456789-           is null             then  numeric              else  alpha         end FROM tab1

User · Answer

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

User · Answer

REGEXP LIKE column       digit          returns TRUE if column holds only numeric characters

User · Answer

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

[sql] How can you tell if a value is not numeric in Oracle?

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to isnumeric