IS NUMBER

From Oracle FAQ
Jump to: navigation, search

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[edit]

Pure SQL solutions usually use TRUNCATE 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[edit]

A more elegant method would be to create a PL/SQL function tat 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[edit]

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