Oracle: Check to see if a value is numeric

One of the functions that MS SQL has that Oracle does not have by default (which can be frustrating at times) is a function that checks to see if a value is numeric. After playing around a bit and digging through oracle in line function documentation I finally found something that would pass for this functionality.

I wanted something that would provide a result of 0 (is not numeric) or 1 (is numeric). Below is a SQL statement that I used during th e"playing" around process.

SELECT
    CASE WHEN nvl(length(translate(trim('111d'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END NonNumeric,
    CASE WHEN nvl(length(translate(trim('1111'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END Numeric
FROM DUAL

The idea behind this code is that first we take a string value (or field) and use the translate function to remove all of the numeric values and characters that are used with numeric values with nothing. Around that value I used the length function to return the length of "translated" value. I then evaluate the length. If it is larger than zero then I return a value of 1, becuase any value with a length greater than zero has a non-numeric character in it. Otherwise it returns a value of 1 (which means that the value is a numeric value).

I attempted to put together syntax to create a function called ISNUMERIC and pasted it below. I do not have an Oracle database on this server or have access to an Oracle in this environment to be able to run this code to make sure that it executes properly. I believe it will work though it has not been tested.

CREATE OR REPLACE FUNCTION ISNUMERIC (VALUE IN VARCHAR2)
    RETURN NUMBER
AS
BEGIN
  IF nvl(length(translate(trim(VALUE), ',.+-0123456789', ' ')),0) > 0
  THEN
    RETURN 0
  ELSE
    RETURN 1
  END IF
END ISNUMERIC;