Concatenating of string columns is not very simple task in T-SQL if you don’t know ANSI standart of it. According to ANSI standart of SQL, concatenating of NULL-value is NULL in the result. It’s not clearly for developer, but it is so.

Let’s see some features for work with concatenating NULL-value columns. For example, I will use tutorial database Northwind and its table Customers.

Execute SQL-query and see results in table below:

 select Address, City, Region, PostalCode, Country from Customers

Address	City	Region	PostalCode	Country
Obere Str. 57	Berlin	NULL	12209	Germany
Avda. de la Constitución 2222	México D.F.	NULL	5021	Mexico
Mataderos  2312	México D.F.	NULL	5023	Mexico
120 Hanover Sq.	London	NULL	WA1 1DP	UK
Berguvsvägen  8	Luleå	NULL	S-958 22	Sweden
Forsterstr. 57	Mannheim	NULL	68306	Germany
24, place Kléber	Strasbourg	NULL	67000	France

 

You can see that there are some values in column Region with NULL values.

Let’s concatenate columns to get full address as one value:

select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

The result is not expected by a common software developer. He expected to see something like "Germany, Berlin, , 12209, Obere Str. 57", but in the result you can see a lot of NULL values (on Figure 1):

 

You can interpret NULL value in columns as empty string using parameter CONCAT_NULL_YIELDS_NULL. By default it’s value is ON, but you can change it to OFF in your session. This query will return more comfortable results:

SET CONCAT_NULL_YIELDS_NULL OFF
select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

 

Don’t forget to set value  CONCAT_NULL_YIELDS_NULL to ON (SET CONCAT_NULL_YIELDS_NULL ON)

Also you can use function COALESCE (expression, value_instead_of_null) , which return value "value_instead_of_null" if expression is NULL. The string for concatenating is like this:

select COALESCE(Country, '') + ', ' +  COALESCE(City, '') + ', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') + ', ' + COALESCE(Address, '') from Customers

The result of this SQL-query is the same as on Figure 2.

 

Since MS SQL Server 2012 you can use function CONCAT (expr1, expr2, expr3, … , exprN)

select CONCAT(Country, ', ', City, ', ', Region, ', ', PostalCode,', ', Address ) from Customers

The result of this SQL-query is also like on Figure 2.

 

It seems like everything’s OK here, but there’s one mark. Instead of columns with NULL value you can see two commas. It’s rather easy to fix it using function REPLACE:

select REPLACE( COALESCE(Country, '') + ', ' +  COALESCE(City, '') + 
			', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') +
			', ' + COALESCE(Address, '') , ', ,', ',') 
			from Customers