The vendor data we load in our staging table is rather dirty. One column in particular captures number data but 40% of the time has garbage characters or random strings.

I have to create a report that filters out value ranges in that column. So, I tried playing with a combination of replace/translate like so

but it fails whenever it encounters a char I did not code. Therefore, the report can never be automated.

Javascript has the isNaN() function to determine whether a value is an illegal number (True if it is and false if not).

How can I do the same thing with DB2?? Do you have any idea?

Thanks in advance.

Wouldn't it be easier to identify numbers instead of not-a-numbers? After all, there are only 10 possible decimal digits.

It is tougher to validate numbers than you might think. Consider -5.38e+6 which is a perfectly valid real number.

-PatP