So what you want is the SQL equivalent of

IF %CHECK(' 0123456789':mystring) = 0

Unfortunately, ANSI and DB2 SQL don't have such a function.

You could create your own scalar function, though. I wouldn't even try to do it in SQL; just call RPG and use %CHECK(). Make it flexible by passing both comparator and base strings as parameters. SQL doesn't have booleans (indicators), so you'll want to return the integer position value given by %CHECK. If it's 0, it wasn't found; otherwise, you have the position of the first non-matching character.

It sounds like he/she is looking for a Filter/Compress routine. I have those in COZTOOLS but for within RPG, not SQL. I know some people have written SQL Functions to compress out non-Alphanumeric characters from a field and return only the "good" data. You'd have to do the same to get the results they expect.

I know it's ugly, but you could test each character for a blank or a numeric digit:

Select * from file where sst(field,1,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,2,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,3,1) in (' ','0','1','2','3','4','5','6','7','8','9') and sst(field,4,1) in (' ','0','1','2','3','4','5','6','7','8','9') and ...

OK, thought of a much better method:

Select * from file where translate(field,'          ','0123456789') = ' '

This will select the records where the field contains only numbers and/or blanks.  If you actually want only numbers 1 - 9 (not including 0), then omit the zero from the list of numbers in the translate function above.

WHERE LENGTH(TRIM(TRANSLATE(TRANSLATE(TRIM(the_field)
        ,'X',' '), ' ', '0123456789'))) = 0

In this code, if the result is zero then there were only numeric values in the field.