The translate function is pretty amazing. You pass in the variable, a string of characters you want the character to be replaced with followed by a string of characters you want to search for. In the example below all numeric characters become N and all N’s become X. This makes all your Digits the character N which you can then compare to ‘NNNNN’ in this instance of a char(5) datatype column to select your numeric only fields, you can then do a numeric comparison such as greater than <10000 without running into a SQL exception with casting nonnumeric characters.
|WHERE TRANSLATE (MYTABLE.MYFIELD, 'NNNNNNNNNNX', '1234567890N') = 'NNNNN' AND MYTABLE.MYFIELD<10000|
|—MYFIELD is a CHAR(5) field and is being compared against 5 'N's|
SQL Reference for IBM i – to learn more about TRANSLATE