How to concatenat just non null value

1

Hi,

I have the following
SELCT ( DEF1 + '|' +DEF2 + '|' +DEF3 + '|' + DEF4 + '|' + DEF5) As DEFVal
FROM TableA

How can I check for NULL for each DEF SO I only DEFF1 + '|' + DEF... that is not null?
For Example, if DEF1 = BOY and DEF4 = Dog and then it would return BOY | Dog in DEFval.

Any thoughts would be appreciated.
Thank You

2

You have to use ISNULL() (if you don't have CONCAT available):

SELECT ( ISNULL(DEF1, '') + ISNULL('|' + DEF2, '') + ISNULL('|' + DEF3, '') + ISNULL ('|' + DEF4, '') + ISNULL('|' + DEF5, '') )
FROM TableA

3

Hi Thanks for the reply, but unfortuantly this is doing the same thing. That is if they were all null for example |||||||. I need it to eliminate the pipes where the value is null.

So in my example if only DEF1 = BOY and DEF4 = Dog had values the only pipe I would see is the one between them.

Thanks

4

Sorry I made a mistake it is working for me.

Thanks
I really apprecate your help

5

SQL Server 2017 and above - you can use CONCAT_WS.

SELECT concat_ws('|', DEF1, DEF2, DEF3, DEF4, DEF5) FROM TableA;
1 Like