List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 28 2003 2:05am
Subject:Re: zip field
View as plain text  
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);
>
>[untested]

Simpler:

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.
>>
>>HTH,
>>=C=
>>* Cal Evans
>>* http://www.christianperformer.com
>>* Stay plugged into your audience
>>----- Original Message -----
>>From: "Robert Bielecki" <robert@stripped>
>>To: <mysql@stripped>
>>Sent: Sunday, April 27, 2003 7:03 AM
>>Subject: zip field
>>
>>
>>Hello,
>>
>>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.
>>
>>Bob


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
Thread
zip fieldRobert Bielecki27 Apr
  • Re: zip fieldPeter Brawley27 Apr
  • Re: zip fieldCal Evans27 Apr
    • Re: zip fieldJeff Shapiro27 Apr
      • Re: zip fieldPaul DuBois28 Apr
  • Re: zip fieldBhavin Vyas27 Apr