Re: Finding invalid data in zoned numeric fields with SQL

That article does not handle the situation described by the OP; i.e. the last question, even if it may mimic the alluded COBOL test. That article addresses character [human readable text\character string] data validation as numeric. The scenario described by the OP starts as invalid binary [en]coded decimal data in a zoned decimal field\column. Accessing the data in the field will in most cases effect a /data mapping error/. The HEX scalar function of the SQL enables access to the hexadecimal character string which is representative of the raw data in the field, without an error; in this case, hex representation of the binary coded decimal [BCD] data.

Regards, Chuck

McGovern, Sean wrote:

Testing for Valid Data Representation in SQL: -

http://www.itjungle.com/fhg/fhg100808-story02.html

Jim Essinger wrote on 13 July 2009 17:08:

I have a file that has bad data showing up in a field. The
file was created by a third party software vendor, and while we
wait for them to find and fix the problem, I need to find a way
to identify those records with invalid data, and replace that
data with zeros. I would like to do this with SQL if I can,
but I don't know how to test a numeric field for the hex value
of "40404040" or blanks. Better yet would be to test "Not
numeric" (I know that COBOL can do that test!).

Is there a way to test if a numeric field contains something
other than a number in SQL?