Since version 4.1 of DB2 the DECIMAL-function has accepted input of type CHAR and type VARCHAR. It was about time then, but I still fell that this feature it greatly overlooked.

Using the DECIMAL-function you are able to convert numeric data in columns defined as CHAR or VARCHAR into columns defined as DECIMAL. And you don't have to write a conversion program to do it. Leading and trailing blanks are ignored or in other words accepted by the function.

If the data in the CHAR or VARCHAR column happens to contain non-numeric data, the SQL-statement terminates with a negative SQL-code. It would have been more ideal to return NULL instead. Then COALESCE or VALUE could have been used to return a more reasonable value, in case you needed that. In this way it would have been much more easy to identify rows containing the non-numeric data.

You can read all about the DECIMAL-function in the SQL reference manual.