Leading zeros 2

[boatguy]

I am trying to insert records from a csv file and one of the fields is zipcode. The issue I am having is that zip codes that start with a leading zero, end up inserting with the zero removed. How can I maintain the zero?

 

[imstillatwork]

numberformat(yourzipcodevar,"00000")

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[FALCONSEYE]

or make the dB field varchar...

http://www.jetansolutions.com

 

[imstillatwork]

good thinking, some international postal codes are alphanumeric...



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

yeah, the only time you should be using numeric datatypes is when you want to perform numeric operations on them

when was the last time you wanted to know the sum of all zipcodes?



r937.com | rudy.ca

 

[jhall01]

yes, zip codes are best stored as strings since mathematical operations won't be used on the data...plus having it as a string will support non US postal codes that contain letters.

Depending on your usage, since zip codes are always 5 characters long, its sometimes better to use char(5) instead of varchar (or int for that matter) to save a little memory. (a byte or two saved per record may not seem alot, till you have 1 million records)

If you also have zip suffixes it is sometimes best to seperate them into a seperate column, but depending on your setup is not always the best option.

 

[daNewfie]

I would make it larger than 5 chars long in the DB if your intending to have people input info outside the US. In canada the postal code is 6 chars long, well, actually 7 cause their is a space between the first 3 chars and the last 3 chars

 

[LyndonOHRC]

One of our databases has postal codes for seven countries. I also keep a column for country code and base my output mask on the country code using a small table that has CountryCode and PostalCodeMask.

Lyndon

 

[daNewfie]

thats a fantastic idea,,, kicking myself right now...

 

[jhall01]

great point Lyndon!