Zip Code Field needs to be an Integer BUT what about leading ZEROES?

[nkomokov]

I have a ZipCode field that needs to be an integer for query purposes, but it lops off the leading zeroes on zips like 03421. Is there a way to make it allow leading zeroes other than changing the datatype to char?

 

[gmmastros]

Leave it as an integer.

The leading zero is ONLY important when people are looking at it, so add the zero's when selecting this data.

Ex.

Select Right('00000' + Convert(VarChar(10), ZipCode), 5)
From Table



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nkomokov]

Soooooo... when doing a search by ZipCode, I would instruct users to not type in the leading zeroes?

 

[SQLDenis]

Run this for fun
declare @zip int, @varZip varchar(5)
select @zip =500, @varZip ='00500'
if @zip = @varZip
print 'yes'
else
print 'no'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Hehe... good point.

Make it char(5) or something but with additional CHECK constraint.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

After second thought... leave it as an integer. Even if user types 00523, this is interpreted as 523 by default.

This of course assumes leading zeros have no other purposes than being a "filler".

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Leave it as an int
An implicit conversion will occur

declare @zip int
select @zip =500
if @zip = '000000000500'
print 'yes'
else
print 'no'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/