One of my very first tips describes how the function DECIMAL is able to translate numeric data in a character string into a numeric format. Unfortunately this function will stop processing and return a negative SQLCODE if the character string contains non-numeric characters. Since then the function TRANSLATE has been added to DB2. TRANSLATE may be used for a variety of things including validation of numeric data.
If you have a column defined as a CHAR or VARCAR and you want to know whether the column contains nothing but numeric values, you can use the following SQL statement:
This SQL statement will return those rows where columnX contains other characters than numeric characters. If the SQL statement does not return any rows then columnX is numeric for all rows. If you know that columnX may contain leading or trailing blanks you can use TRANSLATE(STRIP(columnX)... instead. It is far more difficult to validate numeric data containing dots (.) or commas (,). This cannot be done using TRANSLATE or any other funcion in SQL. DECIMAL can be used for conversion, but unfortunately not for validation.
Please note that a space is translated into a character that is non-blank. I have chosen an asteriks (*), but any character may be used. Without this detail a space will be accepted just like any numeric character. The idea of using TRANSLATE for validation of numeric characters can be extended to any combination of characters. Just remember that TRANSLATE can only validate the presence of characters in a CHAR or VARCHAR column, but not the order of the characters.