Oracle / PLSQL: Test a string for a numeric value

Question: In Oracle, I want to know if a string value is numeric only. How can I do this?

Answer: To test a string for numeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

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

string1

The string value that you are testing.

This solution uses the TRANSLATE, LENGTH, and TRIM functions to test a string for a numeric value if the numeric value is properly formatted. It will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

SELECT LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))) FROM dual;
Result: 1

SELECT LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))) FROM dual;
Result: 2

SELECT LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))) FROM dual;
Result: null

SELECT LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))) FROM dual;
Result: null

Although this first method will test a string if it is a properly formatted number, it would allow a number to pass such as -+1..8++-, which is of course not a number. A better way to test a string for a numeric value is to create your own function that uses the TO_NUMBER function to test the string value.

For example:

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;

This new function called is_number would return 1 if the value is numeric and 0 if the value is NOT numeric. You could execute the is_number function as follows:

SELECT is_number('123') FROM dual;
Result: 1

SELECT is_number('123b') FROM dual;
Result: 0

SELECT is_number('-+1..8++-') FROM dual;
Result: 0

So this method would catch the fact that -+1..8++- is not a valid number.