Last post Nov 22, 2016 08:12 AM by wmec
Nov 20, 2016 11:30 PM|IANotaryServices|LINK
I have a table in MSSQL that holds ZIP codes. The current DataType is INT. I am noticing that leading zeros are being stripped from the value.
As an example:
ZIP code 06351 is inserting into the column without the "0".
I am wondering if I should be using a different DataType for this value instead of INT.
Any suggestions on the best DataType to use for ZIP code values?
I suppose I could format the output on the MVC view using PadLeft, but would rather not have to do that.
I'd rather not use char(5) or varchar(5) because those are strings.
I am considering
MEDIUMINT (5) UNSIGNED ZEROFILL
Nov 21, 2016 03:54 AM|FarhanK|LINK
Pardon my knowledge but as far as i know there isn't any data type MEDIUMINT in SQL Server. So if you have SQL Server as back end you could
1. Format the data while fetching / displaying records.
2. Change data type to Char or Varchar.
3. Use a computed column. example below.
Declare @Tab Table (
ZipCode2 As Right('00000' + Convert(varchar(50), ZipCode), 5)
Insert Into @Tab (ZipCode)
Select * From @Tab
Nov 21, 2016 05:45 AM|Prince Srivastava|LINK
Use datatype varchar for ZipCode.
because it not necessary all zip code is numeric.
Nov 22, 2016 08:12 AM|wmec|LINK
One way to resolve this problem, is to use varchar2 data type instead. Please note that leading 0 WOULD NOT be stored to INT data type.