ADF - concat null values

Question

Hi,

I need help with concatenating 2 string columns, one of which may be empty (null).
The concat function returns NULL if one of the input values is NULL - though I don't find that in the docs.

Do I have to replace all NULL strings with - what?
Or is there a simpler way? (keeping in mind that every additional string operation comes with performance consts).

Thanks in advance!

Best, Ulrike

Answer 1

Hello !

Thank you for posting on Microsoft Learn Q&A.

In mapping data flows concat() returns NULL if any input is NULL. You don’t need to prefill your column in the source just null proof the concat at runtime.

Try :

concat( coalesce(col1, ''), coalesce(col2, '') )

or :

concatWs(' - ', col1, col2)

or :

iif( isNull(col1) && isNull(col2),
     NULL(),
     concat(coalesce(col1,''), coalesce(col2,''))
)