MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

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



fixed

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






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

This mailing list archive is Copyright 1997-2014 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