0

My question is how do you put my zip code 07601 as an integer? I've tried to google several times this question and never got an answer. The reason I'm doing this is to because its important information for something I need to fill out

  • 3
    I don't understand the question. ZIP codes should not be stored as integers. If you were going to incorrectly store a ZIP code as an integer, you'd store the value 7601 and then need to know when you display it that you need to add in the leading 0. Of course, that means that you'd have no way to differentiate between ZIP codes that were incorrectly entered as 4 characters and ZIP codes that were entered correctly with 5 digits and had the leading digit removed. – Justin Cave Jun 21 '16 at 13:10
  • @JustinCave And some even have 2 leading zeroes. And then add in the possibility of doing ZIP+4, hence valid ZipCodes can be either 3 - 5, or 7 - 9 non-zero digits. Please see my related answer for more details and examples. – Solomon Rutzky Jun 21 '16 at 14:31
3

It boils down to database design esp. choosing data types can affect performance as well - storing zipcodes as integers requires more thoughts ! Eg. non US countries like Canada will have letters in zip code. If you store zip codes as integers, when your company does business in Canada, you will have to change the datatype.

I would select integer datatype where any sort of arithmetic (Calculations) comes in play. Adding or subtracting zipcodes is of no value !

Also, US zipcodes have leading zeroes e.g. 08874. For integers, a leading zero is of no value.

I would suggest you store zipcode as varchar(10) and if all your zipcodes are numbers, you can use cast or convert to fill whatever you are filling out.

From msdn :

Character fields can store text information that is not used in mathematical calculations, such as names, addresses, and numbers. For example, phone numbers or zip codes, though they include mostly numbers, are actually best stored as character values.

Also, check out @srutzky's answer 9 digit zip codes are reporting LEN of 12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.