locked
MSSQL Issue. RRS feed

  • Question

  • User-379137218 posted

    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
    Sunday, November 20, 2016 11:30 PM

All replies

  • User-967720686 posted

    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 (
    	ZipCode INT, 
    	ZipCode2 As Right('00000' + Convert(varchar(50), ZipCode), 5)
    )
    
    Insert Into @Tab (ZipCode)
    Values (12345),
    	   (111),
    	   (133),
    	   (444)
    	   
    Select * From @Tab 	   
    	   

    Monday, November 21, 2016 3:54 AM
  • User1413134711 posted

    Hi , 

    Use datatype varchar for ZipCode. 

    because it not necessary all zip code is numeric.

    Monday, November 21, 2016 5:45 AM
  • User364663285 posted

    Hi,
    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.

    Tuesday, November 22, 2016 8:12 AM