concatenation of four column into a single column

[Sandeepch19]

I have four input column, a,b,c and d where in the output column is E
i am doing the concatenation of ac:d to give the output E. But my problem is even if any of the four input column is null the E column should concatenate other three fields and give the output . It should not be null. Only case where it can be null is when all the four fields are null. I have solution for this that is using stage variables. Please let me know if you have any other solutions to this.

[chulett]

Wrap each field in a NullToEmpty() call during the concatenation, no intermediate stage variables required..

[Sandeepch19]

thank you,
But nulltoempty() is not working in 7.5 . stage variable is the only option?

[chulett]

Define 'not working'. It is a perfectly valid function in the 7.x version of the product, one that I made extensive use of back in the day, doing exactly the kind of things you've asked about here.

[ray.wurlod]

You want to allow for null output, so you will need an If..Then..Else to cover the possibility that all source columns are null.

Code: Select all

If IsNull(InLink.colA) And IsNull(InLink.colB) And IsNull(InLink.colC) And IsNull(InLink.colD) Then @NULL Else NullToEmpty(InLink.colA) : NullToEmpty(InLink.colB) : NullToEmpty(InLink.colC) : NullToEmpty(InLink.colD)

[chulett]

... unless you want the result to be an empty string in that case. If so, stick with just the "else" portion.

[ray.wurlod]

Only case where it can be null is when all the four fields are null.

[chulett]

Of course, just offering an alternative. Perhaps an EmptyToNull() wrapper around the whole thing?