All geek questions in one place

SSIS - Derived Columns

I have one small question, I'm a beginner, so please help me.

In my project, I have one text file that has zip4 and zip5 , I want to combine the zip code zip5 , but it has one problem, in some place zip4 is empty (but not null), so when I used the output column with ISNULL(ZIP_4) ? ZIP_5 : (ZIP_5 + "-" + ZIP_4) expression ISNULL(ZIP_4) ? ZIP_5 : (ZIP_5 + "-" + ZIP_4) ISNULL(ZIP_4) ? ZIP_5 : (ZIP_5 + "-" + ZIP_4) , it will return (zip5-) where zip4 empty and I want only zip5 without - .

So please help me.

+4
source share
2 answers

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])

Secondly, crop zip_4 both sides before checking its length.

+4
source

The specific behavior of ANSI is that any operation that includes null saves an explicit test for nullity ( is [not] null ), yields null. Unfortunately, the default behavior of SQL Server is non-standard. So that...

You need to make sure that the following two settings are not on for your stored procedure or are included in your connection before executing a separate request:

  • set ansi_nulls on
  • set concat_nulls on

If you install them in the body of a stored procedure, the settings apply only to this stored procedure; if you set them for the connection (by executing the set statements), they are the same for all the requests made on this connection (except that the stored procedures have their own execution context).

It is a pity that you cannot guarantee that the missing data is always null , not the nil ( '' ) string - this makes the logic simpler.

In any case, if you have the correct `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

gotta do the trick.

Otherwise, if you do not want to enable the correct behavior, you can do something like this. This will also work with standard NULL behavior. I just don't like it as it includes several 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
0
source

Source: https://habr.com/ru/post/1432538/

More articles:


All Articles