Re: SQL: how to filter out spaces in fields defined as packed numeric

On 11 Feb 2013 09:25, CRPence wrote:

<<SNIP>>

Better to do this:

select RRN(A) , A.*
from Datafile as A
where right(hex(OrderQty, 1) = '0' /* note: the char not hex zero */
/* or a more thorough test: not in ('D','F') */
/* or to be entirely accurate: not in ('A','B','C','D','E','F') */
/* I think I have published a UDF here to validate every byte */

<<SNIP>>

I could not find a UDF that I had offered previously. Probably because instead, I had just given the expressions. The expressions alone are not so complex as to require a separate UDF, at least for when the issues are [typically] for ad hoc inquiries about a specific column. The following for example, is a thorough validation of the Packed BCD column OrderQty irrespective of precision and scale and sign:

≥ select rrn(a) , a.*
≥ from Datafile as a
≥ where right(hex(a.OrderQty), 1) not in ('A','B','C','D','E','F')
≥ or translate( left(hex(a.OrderQty), length(hex(a.OrderQty))-1)
≥ , ' ' /* decimal digits to blanks; blank pad */
≥ , '0987654321' ) <> ''

I forgot to clarify before... Use the inverse logic for selecting only the rows with valid decimal data for the OrderQty column, and drop the RRN which is there only to identify which rows are incorrect and thus need correction.

kwds: sql validate packed decimal data udf create function