In SQL Server and Azure, if you need to concatenate two or more strings, you can use the T-SQL CONCAT() function. As with any basic concatenation operation, this function joins the strings together, end-to-end.

But what if you need to add a separator between each string?

For example, you might want to make a comma-separated list of strings. In this case, you’d want to insert a comma in between each string. Like this:

Paris, France

Instead of this:

ParisFrance

Fortunately, T-SQL provides the CONCAT_WS() function that helps you do exactly that. The CONCAT_WS() function works just like the CONCAT() function, except that it takes an extra argument – the separator you’d like to use.

Here’s an example:

SELECT CONCAT_WS(',','Paris', 'France') AS Location;

Result:

Location    
------------
Paris,France

And you can add a space in there if you want:

SELECT CONCAT_WS(', ','Paris', 'France') AS Location;

Result:

Location     
-------------
Paris, France

The Separator

There’s nothing to say that the separator must be a comma. The separator can be an expression of any character type (char, nchar, nvarchar, or varchar).

Here’s the same example as the previous one, except this one uses a different separator.

SELECT CONCAT_WS(' - ','Paris', 'France') AS Location;

Result:

Location      
--------------
Paris - France

A Database Example

Here’s an example of retrieving data from a database, and combining two columns into one, separated by a comma:

SELECT CONCAT_WS(', ', city.Name, country.Name ) AS Location
FROM city
INNER JOIN country ON
city.CountryCode = country.Code
WHERE country.Code = 'THA';

Result:

Location                   
---------------------------
Bangkok, Thailand          
Nonthaburi, Thailand       
Nakhon Ratchasima, Thailand
Chiang Mai, Thailand       
Udon Thani, Thailand       
Hat Yai, Thailand          
Khon Kaen, Thailand        
Pak Kret, Thailand         
Nakhon Sawan, Thailand     
Ubon Ratchathani, Thailand 
Songkhla, Thailand         
Nakhon Pathom, Thailand

NULL Values

If any of the arguments is a NULL value, SQL Server will skip that value and its separator, but it will still process the others.

Example:

SELECT CONCAT_WS(', ','Paris', NULL, 'France') AS Location;

Result:

Location     
-------------
Paris, France

NULL Value Separator

If the separator itself is a NULL value, the concatenation operation will still be performed, but without a separator.

Example:

SELECT CONCAT_WS(NULL,'Paris', NULL, 'France') AS Location;

Result:

Location   
-----------
ParisFrance

This is one of the differences between T-SQL and MySQL (MySQL also has a CONCAT_WS() function). In MySQL, if the separator is a NULL value, the concatenation results in a NULL value.