CONCAT WITH 4 COLUMNS AND EMPTY COLUMN-CodePudding

I need concatenate 4 columns, but, in some cases I have data in these columns and sometimes not:

When to the following columns have no data, my CONCAT returns an empty result:

My code:

SELECT
    {FN CONCAT({FN CONCAT({FN CONCAT({FN CONCAT({FN CONCAT({FN CONCAT(observacao1, '')}, 
    observacao2)}, '')}, observacao3)}, '')}, observacao4)} AS OBSERVACOES 
FROM 
    Fat.CliComplemento2

Can someone help me?

Thanks

CodePudding user response：

try to use

select CONCAT(
    isnull(observacao1,''), /* case when observacao1 is null use empty string */
    isnull(observacao2,''),
    isnull(observacao3,''),
    isnull(observacao4,''))
FROM Fat.CliComplemento2

CodePudding user response：

I found a solution:

SELECT codEmpresa, codCliente, observacao1, observacao2, observacao3, observacao4, {FN CONCAT({FN CONCAT({FN CONCAT(coalesce(observacao1, ' '), coalesce(observacao2, ' '))}, coalesce(observacao3, ' '))}, coalesce(observacao4, ' '))} AS OBSERVAÇÃO
FROM Fat.CliComplemento2