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

Can you put your actual table with data and expected results? — Suraj Kumar, Jan 10 '19 at 04:59
Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — RN92, Jan 10 '19 at 05:03
@SreelalT Check for NULL and do formatting. See answer below. Suraj Kumar's answers will also give you the desired output — RN92, Jan 10 '19 at 05:41

RN92 · Accepted Answer · 2019-01-10T05:47:15.533

4

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;

edited Jan 10 '19 at 05:47

answered Jan 10 '19 at 05:36

RN92

1,380
1
13
32

this is a very long solution – RoMEoMusTDiE Dec 01 '19 at 20:03

Suraj Kumar · Answer 2 · 2019-01-10T05:48:04.460

You can try the following query.

create table tempTable (id int identity(1, 1),col1 char(1), col2 char(1), col3 char(1), col4 char(1))
insert into tempTable values ('A', NULL, 'C', 'D')

select * into #NewTable from(
select id, col1 as Value from tempTable where col1 is not null
union
select id, col2 as Value from tempTable where col2 is not null
union
select id, col3 as Value from tempTable where col3 is not null
union
select id, col4 as Value from tempTable where col4 is not null
)a

SELECT  ID
       ,STUFF((SELECT '| ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM #NewTable 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,1,' ') List_Output
FROM #NewTable t
GROUP BY ID

The output is as shown below

ID  List_Output
---------------
1     A| C| D

If you do not want to put space between values then you can try this

SELECT  ID
       ,STUFF((SELECT '|' + CAST(Value AS VARCHAR(10)) [text()]
         FROM #NewTable 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
FROM #NewTable t
GROUP BY ID

In this case the output will be

ID  List_Output
---------------
1   A|C|D

You can also try the below actual query using stored procedure

create table tblFloor_Master (FloorID int identity(1, 1),
                              FloorCode char(1), 
                              FloorName char(1),
                              BuildingID char(1),
                              HCMLocationCode char(1))

insert into tblFloor_Master values ('A', NULL, 'C', 'D')

GO
--To create a procedure
create proc uspGetConcateValue
@FloorId int
as
BEGIN
    select * into #tblFloor_Master from(
    select FloorId, FloorCode as Value from tblFloor_Master where FloorCode is not null
    union
    select FloorId, FloorName as Value from tblFloor_Master where FloorName is not null
    union
    select FloorId, BuildingID as Value from tblFloor_Master where BuildingID is not null
    union
    select FloorId, HCMLocationCode as Value from tblFloor_Master where HCMLocationCode is not null
    )a

    SELECT  FloorId
           ,STUFF((SELECT '|' + CAST(Value AS VARCHAR(10)) [text()]
             FROM #tblFloor_Master 
             WHERE FloorId = t.FloorId
             FOR XML PATH(''), TYPE)
            .value('.','NVARCHAR(MAX)'),1,1,'') List_Output
    FROM #tblFloor_Master t
    where FloorID = @FloorId
    GROUP BY FloorId
END

Live demo here - Live Demo

This query will also run in lower versions of SQL server where Concat() function is not available. — Suraj Kumar, Jan 10 '19 at 05:53

Robson Patrick · Answer 3 · 2019-12-01T20:07:56.430

0

edited Dec 01 '19 at 20:07

answered Dec 01 '19 at 19:52

Robson Patrick

1
1

tested on postgres – Robson Patrick Dec 01 '19 at 19:54
Welcome to SO! FYI, this question is asking about SQL server (see the tags and title), not Postgres. – robsiemb Dec 01 '19 at 20:16

score 0 · Answer 4 · answered Dec 01 '19 at 20:04

0

Should be simple as doing replace, replace || to | then replace ||| to | since the maximum number of | is 3.

DECLARE @OldValue VARCHAR(8000); 

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

answered Dec 01 '19 at 20:04

RoMEoMusTDiE

4,739
1
17
26

Hi, given all of the items are empty you get ```||||``` before replacing, ```||``` after the inner replace and also ```||``` as final (wrong) result, since you watching for 3 times |. This solution is also wrong for ```|||```(2 adjacent fiels empty). The replace better could be ```||``` to ```|```, two times in a row: ```||||->||->|``` and ```|||->||->|``` for reducing up to 4 ```|``` to only 1. Switching inner and outer replace could solve the issue, too. – FrankM Jul 12 '21 at 08:04

score 0 · Answer 5 · answered Mar 07 '23 at 14:50

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;

Concat Column Values with a separator in SQL Server

5 Answers5