CODEWITHSUNDEEP
sql-servert-sql
Mefhisto1
Mefhisto1

Reputation: 2228

Concatenate columns in t-sql but exclude columns that are null

SELECT 'Column 1: ' + t.Column1 + 
'Column 2: ' + t.Column2 + 
'Column 3: ' + t.Column3 
from Table1 t

So I need a custom string before every column like shown. However, if Column2 is null, for example, I want to exclude the entire part 'Column 2: ' + t.Column2.

Where clause to only show not null columns is not what I'm looking for, since it's valid that all 3 columns are null for some rows.

Upvotes: 0

Views: 213

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

In SQL Server, concatenating null to a string will result with null. Taking advantage of that fact, you can do something like this:

SELECT ISNULL('Column 1: ' + t.Column1, '') + 
       ISNULL('Column 2: ' + t.Column2, '') + 
       ISNULL('Column 3: ' + t.Column3, '') 
FROM Table

In SQL Server 2012 or higher, you can use the Concat built in function, but you still need to concatenate the columns to their hard coded description the old fashion way to take advantage of the effect described before. 

SELECT CONCAT('Column 1: ' + t.Column1, 
              'Column 2: ' + t.Column2, 
              'Column 3: ' + t.Column3) 
FROM Table

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Assuming CONCAT_NULL_YIELDS_NULL is turned on (it is by default and should be), you can do something like:

SELECT COALESCE('Column 1: ' + t.Column1,'') + 
COALESCE('Column 2: ' + t.Column2,'') + 
COALESCE('Column 3: ' + t.Column3,'')
from Table1 t

If any column is NULL then 'Column name ' + Column will be NULL. COALESCE then replaces NULLs with empty strings so that the outer +s are joining non-NULL strings.

Upvotes: 3

Related Questions