RE: Data manipulation

["Sims, Ken" <KSIMS@xxxxxxxxxxxxxxxx>]

Hi Tyra -

>I have a free-form 36 position field with a state and
>postal code (but since its not edited I don't know where
>in the field)..

If it's always the last thing in the field it shouldn't be too hard.  I
would extract the last two sets of non-blank characters in the field.  I
would check those to see if they match the patterns of U.S. or Canadian zip
codes.  For example, if the last set of characters has a length of five and
is all numbers, I would consider it to be a U.S. zip code without zip+4.  If
it has a length of ten with five numbers, a dash, and four more numbers I
would consider it to be a U.S. zip code with zip+4.  After looking at the
data, I might also treat it as a U.S. zip code if there is some other
special character in the sixth position or if it has a length of nine and is
all numbers.

Likewise, if the last set has a length of four and is all numbers, and the
next to last set has a length of five and is all numbers, I might treat the
combination as a U.S. zip code with zip+4 where the separator was a blank.

For Canadian zip codes, I would look at the length and the presence of
numbers and letters in the proper places.  If I remember correctly, Canadian
zip codes usually are separated by a blank, but they might not be in the
file, or they might be separated by a dash or some other character.

If none of these known patterns was matched, I would consider there to be no
zip code information.

Of course, if the zip code is not necessarily the last thing in the field,
or if it sometimes right against the state/province with no intervening
blanks, then it gets more complicated.

Ken
Southern Wine and Spirits of Nevada, Inc.
Opinions expressed are my own and do not necessarily represent the views of
my employer or anyone in their right mind.