RE: SQL find hexadecimal values in a given range

David, using SQL for this is possible but string processing in SQL is
challenging to say the least.
If you just want to find the rows with invalid data, you could leverage the
TRANSLATE built-in.
You define the list of valid characters and then make sure they're the only
ones in the field.
Something like:

SELECT myField,hex(myField)
FROM myFile
WHERE TRANSLATE(UPPER(myField),' ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ')
<> ' '

Of course, the list of valid characters will be greater in your case, but
not by much I think.

But fixing these rows using SQL... even if possible, would be very ugly.
In my opinion, writing a 'fix' program would be much easier in this case.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL find hexadecimal values in a given range

This will show the first position and all rows containing the value hex 40.

SELECT locate( X'40', myfield, 1), myfield FROM myfile WHERE
locate( X'40', myfield)>0

How can I find hex values less that 40?

Thanks