Menu
I expect the column to be a VARCHAR2, in my Oracle Database.
US Zips are 9.
Canadian is 7.
I am thinking 32 characters would be reasonable upper limit
What am I missing?
[EDIT] TIL: 12 is a reasonable answer to the question Thanks to everyone who contributed.
415
The right data type for a postal code is a string. In a SQL database, this would typically be VARCHAR() or CHAR() , of the appropriate length.
You can format a cell or range of cells to display leading characters so that the postal code is preceded by enough characters to fill the cell's width. For example, you can use zeros or dashes to display a postal code as follows: 0000000 98052 or ------- 98052.
United States ZIP codes are five numerical digits long. ZIP+4 codes, also known as add-on or plus-four codes, include four additional digits that identify a geographic segment within a ZIP code delivery area. The first digit in a ZIP code represents a defined group of U.S. states.
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.
If you want to integrate postal codes in database then geonames database is best to use. Even though it is tough to use and understand but it is the largest geographical database available freely to users like us. All the other such database are more or less likely have same data and structure.
OQ: What is the data type for postal address in SQL database? Address is rather entity (set of attributes) than single value. Address can be reflected with several (usually text) values for country, city name, street name and number, zip code and flat number.
UK have published standards: UK Government Data Standards Catalogue Minimum of 2 lines and maximum of 5 lines for the postal delivery point details, plus 1 line for country and 1 line for postcode/zip code If you want to integrate postal codes in database then geonames database is best to use.
Choosing the right data type. (Integer or String) Choosing the format type. (International or National) Verifying the phone number. Best way to store Phone Numbers in a database. Here are the steps which you need to take before you store a phone number in a database. Take input from the user. Identify the country of the user.
Skimming through Wikipedia's Postal Codes page, 32 characters should be more than enough. I would say even 16 characters is good.
104
As already raised by @neil-mcguigan, wikipedia has a decent page on the topic. Based on that 12 characters should do it: http://en.wikipedia.org/wiki/List_of_postal_codes
The wikipedia article lists ~254 countries, which is pretty good regarding UPU (Universal Postal Union) has 192 member countries.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
