[PL/SQL] Test a string for a numeric value

In PL/SQL, you can easily check whether a string contains numeric values with:

LENGTH(TRIM(TRANSLATE(string, '0123456789', ' ')))

This will return null if the string contains only numeric characters otherwise it will return the number of non-numeric characters in it.



It uses:

• TRANSLATE(string, 'chars_to_replace', 'replacement_chars'): we use this function to replaces all occurrences of each numeric character from chars_to_replace in string with its corresponding character in replacement_chars, which in our case is an empty string ''. Remember that the empty string is considered NULL in Oracle.
• TRIM(string): we use this function to remove any leading AND trailing empty spaces from string.
• LENGTH(string): we use this function to count the length of string. If it is an empty string '', this function will return null.

If you want to test whether a single character or fraction of the given string is numeric, you can add:

• SUBSTR(string, starting_position, number_of_chars): to extract the specified number_of_chars from string starting at starting_position. The string begins at index 0.

Like so:

LENGTH(TRIM(TRANSLATE(SUBSTR(string, 0, 1), '0123456789', ' ')))

To check if the first character in string is numeric or not.