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+)?$') 
            then 'numeric'
            else 'alfa'
      end
FROM  tab1;

PL/SQL solution

A more elegant method would be to create a PL/SQL function that can be used for checking:

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;
/

Also see

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