char(??) vs varchar(??)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » char(??) vs varchar(??)

14 posts, Page 2 of 2

««1 2

char(??) vs varchar(??)

Rate Topic

Display Mode

Topic Options

Author

Message

GSquared

Posted Wednesday, October 31, 2012 8:04 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
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

CELKO

Posted Wednesday, October 31, 2012 8:19 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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 BigGrin

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

Jason-299789

Posted Wednesday, October 31, 2012 9:00 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

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

kapil_kk

Posted Thursday, November 01, 2012 5:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

nice discussion Smile

Post #1379738

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions