Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

char(??) vs varchar(??) Expand / Collapse
Posted Wednesday, October 31, 2012 8:04 AM



Group: General Forum Members
Last Login: Wednesday, April 8, 2015 1:57 PM
Points: 13,872, Visits: 9,605
For Zip codes (which are actually a USPS trademark), yeah, 5-digits is fine. Well, unless you need Zip+4. Of course, you can put the +4 in a separate, Char(4), numeric-only, nullable column, to enforce the rules on that.

For international postal codes, you need to get a bit more flexible. Canadian, for example, have numbers and letters, in a particular pattern. Same for UK if I'm not mistaken. As Gail mentioned, SA uses a 4-digit code (all numbers if I'm not mistaken). Other countries, other rules.

Nullable columns, one for each format, can be done, with enforcement of format in each column. ZipCode char(5) numeric-only, ZipPlus4 char(4), numeric-only, PostalCanadian char(6) with formatting enforced, and so on. But that's a little complex to manage.

Could do a table per format (some formats are multinational), with limits on the ISO3 country code in each table to map that to relevant national laws, then Union with forced-null columns for columns that some countries have and others don't (like State/Province/Region) into a single view for querying.

Could create a CLR datatype "MailableAddress" that could format, enforce, etc., based on rules built into the DLL.

The right XML rules could also be built to enforce a full set of international address rules.

Or you could use nvarchar(10) for a column called PostalCode, and manage the input/update rules in a higher software layer. Preferably the UI, where an error in Zip code format can be brought to the user's attention and they have a choice of fixing it or overriding the rule.

Lots of ways to solve these things.

(I've been working with address list data for over 10 years. It's a bit more complex than char(5) and a Like '[0-9][0-9][0-9][0-9][0-9]' constraint, unless you're going to completely disallow non-US addresses.)

But Joe is right that you need to know what you're planning on storing and not just blanket varchar(50) or char(25) everything.

Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1379327
Posted Wednesday, October 31, 2012 8:19 AM



Group: General Forum Members
Last Login: Today @ 7:08 PM
Points: 1,945, Visits: 3,496
Then you have UK postal codes which are 5-8 characters long, depending on whether you include a space or not.

Oh, the UK is even worse than that! Try to write a regular expression to validate it! It is an alphanumeric nightmare. They finally introduced a MailSort code for commercial bulk mailers, but not the general public.

Korea is replacing the Japanese street address system with the US 9-1-1 system, but the Buddhist are protesting it. The old system is based on "neighborhoods" (chome) and numbering the building within the boundaries in the order they were built! But some of the chome names are Buddhist saints and they are afraid they will be lost. Kinda like small town post offices in the US

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1379340
Posted Wednesday, October 31, 2012 9:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:21 AM
Points: 1,042, Visits: 3,029
True, UK postal codes are a nightmare to handle, thats why most people will try and push it up into the UI layer and rely on specialised Reg-ex validation procedure before they get to the Db layer, or use a Postal code tool that uses the Royal Mail PAF files to validate the addresses.

Though the later is often out of date especially for new developments as its only updated every quarter.

SSC Guide to Posting and Best Practices
Post #1379368
Posted Thursday, November 1, 2012 5:13 AM



Group: General Forum Members
Last Login: Monday, April 20, 2015 6:08 AM
Points: 2,027, Visits: 2,528
nice discussion

To get quick answer follow this link:
Post #1379738
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse