Zip Code Formats

[gfisch13]

Hi everyone -

I have a Zip Code field in my data that has inconsistent data.  I need to parse several formats but I'm not sure how to go about it.

the field is a 9 character text field, my formats look like the following:

12345

123450000

          12345

             7751    (this one drops a leading zero, its 07751 in NJ)

I'm thinking I need some type of conditional, but I'm not sure of the functions to use, nor do I understand how to place this in the load script.   My goal is to end up with just the 5 digits of the zip code.

Any input is appreciated - thanks!

[Arthur_Fong · Accepted solution]

Try this:

Raw:
load if(len(PostCode)<5,num(PostCode,'00000'),if(len(PostCode)>5,left(PostCode,5),PostCode))as PostCode inline [
PostCode
123450000
34567
7751
];

[gfisch13]

This worked perfectly - thanks Arthur!

[gfisch13]

Arthur - Here's an additional question that stems from a deeper review of the data.   This function worked, but I found a situation where the Zip field had 8 digits, where there should have been 9, which means that the leading 0 has been dropped.

Can I insert additional conditions into what you sent to account for this?

my Zip field is reading:   77510000

when I use the function you provided I get:   77510, which is correct based on your programming but not correct based on what the value should be.

I should get:   07751

So hence my question of adding additional conditions.

I've used this function in my LOAD statement.  My data loads from QVD's.  While the script runs all the way through the results in my field are either 0 or -1.

if(len(EMPE_ZIP_CD)<5,num(EMPE_ZIP_CD, '00000'),if(len(EMPE_ZIP_CD)>5,left(EMPE_ZIP_CD,5), EMPE_ZIP_CD)) or
if(len(EMPE_ZIP_CD)=8,left(num#(EMPE_ZIP_CD,'00000'),4), EMPE_ZIP_CD) as EE_Zip

Would really appreciate any additional help.

Thanks!

[Arthur_Fong]

Try this:

Raw:
load
if(len(PostCode)<5,num(PostCode,'00000'),
if(len(PostCode)>5 and len(PostCode)<>8,left(PostCode,5),
if(len(PostCode)>5 and len(PostCode)=8,left(num(PostCode,'000000000'),5),
PostCode)))as PostCode
inline [
PostCode
123450000
34567
7751
77510000
];