Hi,
To check valid data within your string, you may try something like this:
Select Case When Length(Trim(Translate(Substr(YourString, StrPosPacked,
LengthPacked-1), ' ', '1234567890'))) > 0
or Substr(a, LastPosPacked, 1) not in ('F', 'D')
Then 'Invalid Packed Data'
Else 'Valid Packed Data'
End
From YourTable
;;
StrPosPacked is the begin of your packed data
LengthPacked is the length of the packed data (in my example the last
character is stripped off because I only wanted to check if the rest is
numeric.
LastPosPacked is the last character of the packed data, which must bei
either F or C or D for negative data
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Dean.Eshleman@xxxxxxxxxxxxxx
Gesendet: Thursday, 02. July 2009 22:40
An: midrange-l@xxxxxxxxxxxx
Betreff: Validating packed data in a character field
Hi,
One of our files in a third party application is getting corrupt data
loaded into one of the fields. The field is a 13 position field and it
contains 4 fields within it. These 4 fields are the key to a file. The
first 3 positions are character, the next 2 are a signed number, the next
3 are a signed number and the final 5 are an 8 digit packed number. It is
the 8 digit number that is corrupted sometimes. Is there an SQL statement
that I can use to find the corrupt numbers? I would want to report them
so that they can be fixed.
Dean Eshleman,
MMA, Inc.
midrange.com
