Concatinating the String with NULL

[rkumar28]

Hi,

I have a situation to concatinate a hardcoded string with another. Something like below:

'<ISP>':DSLink3.Table_column1:'</ISP> ':'<DI>':DSLink3.Table_column2:'</DI>

I have to pass this as one string per row in a table. But the problem is: if any of the above columns in a table(Table_column1 or Table_column2) is null...the whole string does not populate. I want to populate the string even if the column1 and column2 is null. Something like below:

<ISP></ISP><DI></DI> ---> I am tryign to achieve this in case of null values for columns Table_column1 and Table_column2.

Is there a way to concat the string with a null value.

I will really appreciate any advice and recommendation in this regards.

Thanks
Raj

[chulett]

Is there a way to concat the string with a null value.

No. Null is the unknown value, so adding null into any mix results in null as the output. You need to either explictly convert the nulls to something else or account for them in some if-then-else logic.

Check out the Built-In Transform NullToEmpty for one example of how to easily handle your problem.

[ray.wurlod]

Technically what Craig says is correct. If you append, or prepend, something unknown, then the result is necessarily unknown. You need to convert the null to "" or 0, depending on the underlying data type. There are SDK Transforms for doing exactly this.

DataStage does, however, let you concatenate its internal representation of null, by using the system variable @NULL.STR.

Code: Select all

inlink.ColName : @NULL.STR

You have to figure out whether that result is meaningful in any way to you; I would assert that it isn't, and have only posted in the interests of completeness.