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

Wow - thanks! Very rigorous and complete.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, February 11, 2013 1:04 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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