At 8:50 -0600 4/27/03, Jeff Shapiro wrote:
>Yes, all US zip codes are a minimum of 5 digits; however, all zip
>codes northeast of New York state begin with a 0. When converting
>from numbers to strings the leading 0 is lost. Therefore, making the
>zip code only 4 digits causing problems when you need to display and
>print the zip code, and depending on the program or programming
>language, sorting as well.
>To regain your lead 0, try doing something like the following before
>you add any Canadian codes:
>UPDATE your_table SET zip = CAT('0', zip) WHERE LENGTH(zip) < 5);
UPDATE your_table SET zip = LPAD(zip,5,'0');
>At 8:14 -0500 4/27/03, Cal Evans wrote:
>>You are going to lose them in the conversion. After making the change issue
>>an update to leftpad the zips with '0' to get them back. However, you
>>would be better off NOT left padding them but simply let them alone. I know
>>of no US zipcodes that are not of the minimum 5 digits. Therefore they
>>should always sort properly.
>>* Cal Evans
>>* Stay plugged into your audience
>>----- Original Message -----
>>From: "Robert Bielecki" <robert@stripped>
>>Sent: Sunday, April 27, 2003 7:03 AM
>>Subject: zip field
>>I'm using MySQL 3.23.51.
>>I have a zipcode field defined as:
>>zip int(5) unsigned zerofill NOT NULL default '00000',
>>However, it's now necessary for us to accept Canadian postal codes.
>>If I change the zipcode field to:
>>zip varchar(5) NOT NULL default ' ',
>>I lose the leading zero on all zips beginning with zero
>>Any ideas on how I can change the current field to accept the Canadian
>>postal code format without screwing up the current zip data?
>>Thank you in advance for any assistance.