Testing a string for a numeric value

Sometimes it is necessary to determine if a value in a varchar column actually contains a number. The code below gives examples of how to do this.

All versions of Oracle

The code below created a stored procedure that can be used on all versions of Oracle to test whether a string contains a numeric value. This stored procedure can be called from either SQL or PL/SQL.

CREATE OR REPLACE FUNCTION is_numeric( p_char varchar2 ) return varchar2 IS
BEGIN 
  if (to_number(p_char) is not null) then
    return 'Y'; 
  ELSE
    return 'N';
  end if; 
EXCEPTION
  when others then return 'N';
END; 
/

This can be used from SQL like so...

select ..... where is_numeric(<column>) = 'Y';

Oracle 10g and Above

Oracle 10g supports the use of regular expressions, so although the PL/SQL procedure above could still be used it will be more efficient to use a regular expression to test for a numeric value. An example of using REGEXP_LIKE from within SQL to detect a numeric value is shown below:

select ..... where regexp_like(<column> , '^-?[0-9]*\.?[0-9]*$');