englishteeth.co.uk

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 preferrable 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