question

Your best bet would be to convert the `Zipcode` field to a `VARCHAR(15)` datatype instead of using `INT`. Zipcodes are not numbers, even though they are comprised of mostly numbers, and quite often used in short-form (ZIP instead of ZIP+4) are only numbers. But, zipcodes in the US sometimes have leading zeroes (e.g. `00123`), and using the ZIP+4 adds a dash (e.g. `00123-2400`). Postal Codes in many other countries also allow for using US-English letters A - Z (e.g. `B7E 9T5`) as well as sometimes using a space or dash. Then, once you are using the proper `VARCHAR(15)` datatype, a missing zipcode is simply an empty string. And just in case this was a reason to use `INT`, you aren't really saving much, if any, space using an `INT` instead of a `VARCHAR`. While most values will be 5 characters, and hence 5 bytes for `VARCHAR`, missing entries will be 0 bytes as that is what an empty string uses. An `INT` is always 4 bytes, whether you have a 5 digit number or a 0. So most rows will be 1 more byte in `VARCHAR` than for the `INT`, but empty rows will be 4 bytes less. So it mostly evens out, and then you don't have to deal with formatting issues such you are facing now, and you can accommodate ZIP+4 (and any type of Postal Code that exists). Please see the following for more info on Postal Codes: * [Standard Postal Service State Abbreviations and ZIP Codes][1] * [List of postal codes][2] Of course, if you are using SSIS to migrate the `INT` field into a proper `VARCHAR` field, then @Wilfred's answer (regarding using `NULLIF` ) should work :-). [1]: https://www.irs.gov/pub/irs-utl/zip%20code%20and%20state%20abbreviations.pdf [2]: https://en.wikipedia.org/wiki/List_of_postal_codes