Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the best column type for a United States ZIP code?

Tags:

int

mysql

varchar

I want to store Zip Code (within United States) in MySQL database. Saving space is a priority. which is better option using VARCHAR - limited to maximum length of 6 digit or using INT or using MEDIUM Int . The Zip code will not be used for any calculation. The Zip code will be used to insert (Once), Updated (if required) and Retrieved - Once (but it can be more than once) .

Which is better option to use here VARCHAR or INT or MEDIUM IN MYSQL ? Please suggest anything else ?

like image 437
Tapan Banker Avatar asked Apr 05 '13 21:04

Tapan Banker


People also ask

What data type should you prefer for ZIP code field in a table?

b Number data type should be chosen for a zip code field in a table.

What is the format for ZIP code in USA?

A United States postal code, for example, might be either five digits or five digits followed a hyphen (dash) and another four digits (12345-1234). If you are validating US postal codes, allow for both conditions. The following rule validates a field that can be five digits, five digits + dash + 4 digits, or blank.

What data type is generally preferred for a 5 digit ZIP code?

Numeric, i.e. 32-bit integer.

What data type is ZIP code in SQL?

You can use varchar column type for Zipcode in SQL Server. Varchar data type accept both numbers and characters.


2 Answers

There are a few problems with storing a zip code as a numeric value.

  1. Zip Codes have extensions, meaning they can be 12345-6789. You cannot store a dash in a numeric datatype.
  2. There are many zip codes that start with a zero, if you store it as an int you are going to lose the leading zero.
  3. You do not add/subtract, etc zip codes or use numeric functions with them.

I would store a zip code as a varchar(5) or varchar(10).

As a side note, I am not sure why you would select varchar(6), do you have a reason for selecting an unusual length when standard zip codes are 5 or 10 with the extension?

like image 100
Taryn Avatar answered Oct 11 '22 12:10

Taryn


I usually use MEDIUMINT(5) ZEROFILL for 5 digit zip codes. This preserves any leading 0s and it only uses 3 bytes where a VARCHAR(5) would use 6. This assumes that you don't need the extended zip codes, which have a dash and 4 extra numbers. If you were to decide to use a textual type, I would use CHAR(5) instead of VARCHAR(5) since it is better if the data is always 5 characters long.

like image 33
G-Nugget Avatar answered Oct 11 '22 14:10

G-Nugget