question

vaka avatar image
vaka asked

How can I Replace 0 with NULL in ssis for Zipcode(Integer data type) Column

How can I Replace 0 with NULL in ssis for Zipcode(Integer data type) Column
replacederived-column
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Can't you use the T-SQL function NULLIF()? NULLIF(Zipcode, 0)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you so much for your detailed information.... I have used nullif and cast together .... Worked for me . Source value is varchar and destination is int ... I used SSIS to move source to destination. Thank you all
0 Likes 0 ·
@vaka If this solution worked for you, then please "accept" the answer (there should be a check mark or something).
0 Likes 0 ·
srutzky avatar image
srutzky answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.