AIX 5L
DB2 8.2

Here is the problem I have.


I have a table with a column that contains strings and numbers.

Ex:

90
85
lt30
EMPTY
15
10

I want to pull back records that are greater than 80
select * from tableA where INT(col1) > 80

but as soon as the query reaches a string that can't be converted to an Integer it bombs out

SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

Is there a way through the WHERE clause, INT conversion, CASE stmt, I can ignore the string as another record that evaluates false, so the query completes with a result set of

90
85


Thanks in advance,

Charlie

Still not sure how this works, but it works beautifully. Thanks.

It replaces every digit in the string with a blank. If what's left is a blank string, it means there was nothing but digits, therefore it can be cast to an integer without errors.