Concatenate multiple columns, with null values

Question

I want to concatenate multiple columns, separated by ;, in MS-SQL Server 2008.

The problem that I have is that without the CONCAT() I don't know how to handle NULL columns and not have results like this tattoos;comics;;;

Here you have the script to create the sample data:

declare @tbl as table (
    id int
    ,kw1 varchar(15)
    ,kw2 varchar(15)
    ,kw3 varchar(15)
    ,kw4 varchar(15)
    ,kw5 varchar(15)
);

insert into @tbl values
(1, 'innocence', 'graphic novel', 'cartoon', NULL, 'comics')
,(2, 'tattoos', 'comics', NULL, NULL, NULL)
,(3, NULL, 'music', 'cartoon', 'adventure', 'film')

And the table:

+----+-----------+---------------+---------+-----------+--------+
| id |    kw1    |      kw2      |   kw3   |    kw4    |  kw5   |
+----+-----------+---------------+---------+-----------+--------+
|  1 | innocence | graphic novel | cartoon | NULL      | comics |
|  2 | tattoos   | comics        | NULL    | NULL      | NULL   |
|  3 | NULL      | music         | cartoon | adventure | film   |
+----+-----------+---------------+---------+-----------+--------+

So my actual result is this:

+----+-----------------------------------------+
| id |                Keywords                 |
+----+-----------------------------------------+
|  1 | innocence;graphic novel;cartoon;;comics |
|  2 | tattoos;comics;;;                       |
|  3 | ;music;cartoon;adventure;film           |
+----+-----------------------------------------+

But this is what i want:

+----+----------------------------------------+
| id |                Keywords                |
+----+----------------------------------------+
|  1 | innocence;graphic novel;cartoon;comics |
|  2 | tattoos;comics                         |
|  3 | music;cartoon;adventure;film           |
+----+----------------------------------------+

Query:

SET CONCAT_NULL_YIELDS_NULL OFF;

select
    id
    ,kw1 + ';' + kw2 + ';' + kw3 + ';' + kw4 + ';' + kw5 as Keywords
FROM @tbl

Any help is appreciated!

@Tanner is the simple concatenation with +, but i edited my question — Valerica
– Valerica, Commented Jan 18, 2018 at 10:02
helps people to work on a solution and highlight your problems though — Tanner
– Tanner, Commented Jan 18, 2018 at 10:03
If you concatenate with +, a null value in any column will result in a null, not in ;; — HoneyBadger
– HoneyBadger, Commented Jan 18, 2018 at 10:03
@HoneyBadger This is what I thought so, but this is the result that I get — Valerica
– Valerica, Commented Jan 18, 2018 at 10:05

mxmissile · Accepted Answer · 2021-08-13 13:52:49Z

In the absence of CONCAT() (SQL Server 2008 R2+) you can use ISNULL() like so:

SELECT 
    t.id ,
    ISNULL(t.kw1 + ';', '') + ISNULL(t.kw2 + ';', '') +
    ISNULL(t.kw3 + ';', '') + ISNULL(t.kw4 + ';', '') +
    ISNULL(t.kw5 + ';', '') AS Vals
FROM
    @tbl AS t;

If the column value is NULL then the joining of NULL + ';' would produce NULL, therefore giving you the empty string instead ''.

For 2008 R2+ you'd use CONCAT() like so:

SELECT 
    t.id ,
    CONCAT(t.kw1 + ';' ,t.kw2 + ';',t.kw3 + ';' ,t.kw4 + ';', t.kw5+ ';') as Vals
FROM 
    @tbl AS t

Both produce this result:

id          Vals
----------- -----------------------------------------------
1           innocence;graphic novel;cartoon;comics;
2           tattoos;comics;
3           music;cartoon;adventure;film;

Shushil Bohara · Accepted Answer · 2018-01-18 11:42:35Z

For now, I found only the way of getting output exactly what you have mentioned using CASE so you can try and use it:

SELECT
id,
keywords = (
CASE WHEN kw1 IS NOT NULL THEN kw1 + CASE WHEN COALESCE(kw2,kw3,kw4,kw5) IS NOT NULL THEN ';' ELSE '' END ELSE '' END +
CASE WHEN kw2 IS NOT NULL THEN kw2 + CASE WHEN COALESCE(kw3,kw4,kw5) IS NOT NULL THEN ';' ELSE '' END ELSE '' END +
CASE WHEN kw3 IS NOT NULL THEN kw3 + CASE WHEN COALESCE(kw4,kw5) IS NOT NULL THEN ';' ELSE '' END ELSE '' END +
CASE WHEN kw4 IS NOT NULL THEN kw4 + CASE WHEN kw5 IS NOT NULL THEN ';' ELSE '' END ELSE '' END +
CASE WHEN kw5 IS NOT NULL THEN kw5 ELSE '' END)
FROM @tbl

OUTPUT:

id  keywords
-----------------------------------------
1   innocence;graphic novel;cartoon;comics
2   tattoos;comics
3   music;cartoon;adventure;film

Red Devil · Accepted Answer · 2018-01-18 11:46:53Z

-1

You can try this :

     select ISNULL(kwa1,'') + ';' + ISNULL(kw2,'') from TABLENAME

edited Jan 18, 2018 at 11:46

answered Jan 18, 2018 at 10:06

Red Devil

2,4135 gold badges30 silver badges46 bronze badges

Collectives™ on Stack Overflow

Concatenate multiple columns, with null values

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related