Is a string a valid number

Question:

Does PL/SQL offer an is_number function (like isNumber in VB) that will tell me whether a string is a valid number?

Answer:

Oracle does not provide a function to tell you if a string is a valid number, but you can easily determine that information by using the TO_NUMBER conversion function. The basic algorithm is: if Oracle can convert the string to a number, it must be a valid number!
Here is an example of such a function, defined within the check_string package that was posted as a solution for the "String Theory" puzzle:
FUNCTION is_number (
str_in IN VARCHAR2
, numeric_format_in IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN
IS
dummy NUMBER;
BEGIN
IF str_in IS NULL
THEN
RETURN NULL;
END IF;

IF numeric_format_in IS NULL
THEN
dummy := TO_NUMBER (str_in);
ELSE
dummy := TO_NUMBER (str_in, numeric_format_in);
END IF;

RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END is_number;
Note that while this implementation allows you to provide a format mask with which to convert the string, it does not support the ability to pass a value for the nlsparms parameter of TO_NUMBER. This parameter allows you to specify the characters used with number format elements, including the decimal character, group separator, local currency symbol, and international currency symbol. You can easily extend check_string.is_number to pass this value as well.
Here is a block of code that exercises check_string.is_number:
BEGIN
IF check_string.is_number ('1000')
THEN
DBMS_OUTPUT.put_line ('1000 IS a number');
END IF;

IF NOT check_string.is_number ('abc')
THEN
DBMS_OUTPUT.put_line ('abc IS NOT a number');
END IF;

IF check_string.is_number ('$100.50', '$99999.99')
THEN
DBMS_OUTPUT.put_line ('Handles numbers with currency symbols!');
END IF;

IF check_string.is_number ('975-', '9999MI')
THEN
DBMS_OUTPUT.put_line
('Handles numbers with negative sign at end of string!');
END IF;
END;
/
Finally, check out the is_number.tst file for a unit testing script that exercises the function and automatically reports on the status of each test case (of which only a partial set have been defined).
For more information on TO_NUMBER, read the Oracle doc.
Note that if you do not provide values for the format mask or nlsparms, then TO_NUMBER will use the default values for your session.