MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » February 2013

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:

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 */


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

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact