Concatenate multiple columns, with null values

Valerica Published at Dev

Valerica

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

In the absence of CONCAT() (SQL Server 2008+) 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+ 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;

Collected from the Internet

Please contact [email protected] to delete if infringement.