Concat Column Values with a separator in SQL Server

Question

I want to concatenate column values with a separator and assign it to variable.

If column value is null, there's no need to add separator.

For example: A|B|C|D

If B is null A|C|D.

I tried with CONCAT function, but if B is null, it results in A||C|D

DECLARE @OldValue VARCHAR(8000); 

SELECT @OldValue =  CONCAT([FloorCode],'|',
                           [FloorName],'|',
                           [BuildingID],'|',
                           [HCMLocationCode],'|',
                           [IsActive]) 
FROM tblFloor_Master 
WHERE  FloorID = @FloorID; 

@FloorID is an input parameter of SP

Answer 1

SELECT @OldValue = CONCAT('',
    CASE WHEN [FloorCode] IS NULL THEN '' ELSE CONCAT([FloorCode],'|') END,
    CASE WHEN [FloorName] IS NULL THEN '' ELSE CONCAT([FloorName],'|') END,
    CASE WHEN [BuildingID] IS NULL THEN '' ELSE CONCAT([BuildingID],'|') END,
    CASE WHEN [HCMLocationCode] IS NULL THEN '' ELSE CONCAT([HCMLocationCode],'|') END,
    [IsActive])
FROM tblFloor_Master 
WHERE  FloorID = @FloorID; 

Answer 2

I've only just discovered it too, but the CONCAT_WS (With Separator) function is designed for this problem. It has been available since SQL Server 2017.

DECLARE @OldValue VARCHAR(8000); 

SELECT @OldValue =  CONCAT_WS('|', 
                           [FloorCode],
                           [FloorName],
                           [BuildingID],
                           [HCMLocationCode],
                           [IsActive]) 
FROM tblFloor_Master 
WHERE  FloorID = @FloorID;