Oracle "Is Numeric"

February 20, 2008 1-minute read

sql • pl/sql • oracle

Currently Oracle (10g and earlier) does not provide a function to test if a value is numeric. However, it is relatively simple to implement.

The following clause will return the number of non numeric characters found in a string:

LENGTH(TRANSLATE(TRIM(string1), ' +-.0123456789', ' '))

A non zero result means the string is not numeric.

SELECT string_column AS Numbers 
FROM table_of_strings 
WHERE LENGTH(TRANSLATE(TRIM(string_column), ' +-.0123456789', ' ')) = 0;

Depending on environmental constraints, it may be preferable to implement a function:

create or replace function getNumberFromString(inputString in varchar2) return number is 
  numberValue number; 
begin 
  numberValue := to_number(inputString); 
  return numberValue ; 
exception 
  when VALUE_ERROR then return null; 
end;

This could have been implemented as an “isNumeric” function returning a boolean result. However, this currently would not be supported in SQL that could otherwise benefit.

SELECT string_column AS Numbers 
FROM table_of_strings 
WHERE getNumberFromString(string_column) IS NOT NULL;