SSIS - Derived Columns

Question

I have one small question, I am new so please help me.

In my project I have one text file which has zip4 and zip5 , I want to concatenate both the zip code but it has one problem, at some place zip4 is blank (not null) so when I used the derived column with

ISNULL(ZIP_4) ? ZIP_5 : (ZIP_5 + "-" + ZIP_4)

expression it will return the

(zip5-)

where zip4 is blank and I want only zip5 without -.

So please help me.

Are you trying to concatenate in Sql Server or SSIS package? — Praveen, Sep 05 '12 at 17:30

score 4 · Accepted Answer · edited Sep 23 '15 at 14:30

4

Try

ISNULL([ZIP_4]) || LEN([ZIP_4]) == 0  ? [ZIP_5] : ([ZIP_5] + "-" + [ZIP_4])

or

ISNULL([ZIP_4]) || LEN(RTRIM(LTRIM([ZIP_4]))) == 0  ? [ZIP_5] : ([ZIP_5] + "-" + [ZIP_4])

Second one will trim zip_4 both sides before checking its length.

edited Sep 23 '15 at 14:30

Mike G

4,232
9
40
66

answered Sep 05 '12 at 17:39

Piotr Sobiegraj

1,775
16
26

score 0 · Answer 2 · answered Sep 05 '12 at 17:50

ANSI defined behaviour is that any operation involving null save an explicit test for nullity (is [not] null) yields null. Unfortunately, SQL Server's default behavior is non-standard. So...

You need to ensure that the following two settings are on for your stored procedure or turned on in your connection before you execute a standalone query:

set ansi_nulls on
set concat_nulls on

If you set these on in the body of the stored procedure, the settings only apply within that stored procedure; if you set them on for the connection (by executing the set statements), they are one for all queries executed on that connection (excepting that stored procedures have their own execution context).

It's a pity that you can't guarantee that missing data is always null rather than a nil string ('') — it makes the logic simpler.

Anyway, once you have proper `null behavior enabled, something like

-- if missing data is always NULL, do this
select zip9 = t1.zip5 + coalesce( '-'+t1.zip4 , '' )
from someTable t1

or

-- if missing data might be nil ('') or NULL, do this
select zip9 = t1.zip5
            + coalesce(
                '-'
                + case coalesce(t1.zip4,'') when '' then null else t1.zip4 end ,
                ''
                )
from someTable t1

should do the trick.

Otherwise, if you don't want to enable correct behavior, you can do something like this. This will work with standard NULL behavior as well. I just don't like it since it involves multiple tests. But TMTOWTDI, as they say.

select zip9 = t1.zip5
            + case
                when t1.zip4 = ''    then ''
                when t1.zip4 is null then ''
                else '-' + t1.zip4
              end
from someTable t1

To address "pity that you can't guarantee that missing data is always null rather than a nil string" you can always use the [NULLIF](http://msdn.microsoft.com/en-us/library/ms177562.aspx) to coerce the empty string to null value. — billinkc, Sep 05 '12 at 18:27
@billinkc: what do you think the standard function `coalesce()` does? — Nicholas Carey, Sep 05 '12 at 18:30
My apologies, brain went south as I was commenting. NULLIF will convert something to NULL if values match, so NULLIF(t1.zip4,'') would return NULL for nil strings as well as NULL values. This allows you to use your first code snippet `t1.zip5 + coalesce('-'+NULLIF(t1.zip4, ''), '')` instead of your more verbose case statement. — billinkc, Sep 05 '12 at 19:04

SSIS - Derived Columns

2 Answers2