SQL Server Performance Forum – Threads Archive

ZIP code data type

What is the best suggestion for ZIP code data type in USA? USA has different formats for ZIP code. It is as either ##### or #####-####. Am I right? Our current DB has Numeric 5(9,0) and Numeric 9(18,0) for ZIP code. If I choose VarChar(10), then do you think I may lose data during the conversion? Do you suggest me any code for this conversion or just do it in EM? CanadaDBA

can’t think of anything better than VarChar since you want to store the dash in there too (#####-####). I have a few varchar in my database and never have any error coverting some number from varchar. May the best cheaters win

The format (#####-####) is a suggestion only. In fact I am asking about USA Zip codes and
the best idea to handle it.

quote:Originally posted by tdong can’t think of anything better than VarChar since you want to store the dash in there too (#####-####). I have a few varchar in my database and never have any error coverting some number from varchar. May the best cheaters win

CanadaDBA

The answer is still Varchar for USA Zip code hehhehe <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> the thing is if the ZipCode start with a Zero <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> then Varchar save you however number search is faster <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> no need to convert. Still I would use Varchar for Zip Code since I live in Canada and the Zip is number and Characters May the best cheaters win

In Canada we have only 6 characters but in US they have different Zip code versions. Some of them are 5 char a new version 9 and I am not sure the latest version is 10 or 11 char. I think VarChar is the answer. If I don’t want to use EM, should ALTER TABLE sovle my problem? I want to write a script to do the table change. If so, does it automatically convert data? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by tdong The answer is still Varchar for USA Zip code hehhehe <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> … <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

Number to Varchar it should be ok and yes it will convert data automatically <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> May the best cheaters win

Number to varchar conversion does convert data automatically, but you have to manually (or through a script) fill the zeros for the numbers that should start with ‘zero’.

]]>