CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssisderived-column
343
343

Reputation: 305

SSIS - Derived Columns

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.

Upvotes: 4

Views: 2535

Answers (2)

Piotr Sobiegraj
Piotr Sobiegraj

Reputation: 1783

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.

Upvotes: 4

Nicholas Carey
Nicholas Carey

Reputation: 74177

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

Upvotes: 0

Related Questions