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.
SELECT * | |
FROM MYTABLE | |
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
ftp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/db2s0e70.pdf