08/06/2012

[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.

No comments:

Post a Comment

With great power comes great responsibility