IS NUMBER

Oracle doesn't have a built-in IS_NUMERIC function to check is a value is numeric, but over the years people developed innovative alternatives. This page will discuss some of them:

SQL solution

Pure SQL solutions usually use TRANSLATE or REGEXP_LIKE to identify numeric data. Some examples:

SELECT case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
       end
FROM tab1;

SELECT case when regexp_like(col1, '^\-?\d+(\.\d+)?$(E(\+|\-)\d+)?$') 
            then 'numeric'
            else 'alfa'
      end
FROM  tab1;

PL/SQL solution

You can create a PL/SQL function for checking returning 1 if, given the current nls_numeric_characters value, the passed string represents a number and 0 if does not:

CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
  RETURN INT
IS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_string);
  RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
  RETURN 0;
END is_number;
/

Example:

SQL> alter session set nls_numeric_characters='.,';

Session altered.

SQL> select is_number('1,1') from dual;
IS_NUMBER('1,1')
----------------
               0

SQL> alter session set nls_numeric_characters=',.';

Session altered.

SQL> select is_number('1,1') from dual;
IS_NUMBER('1,1')
----------------
               1

Also see

• TO_NUMBER, function to convert a string to a number value.