How to Use the SQL Server CONCAT Function

SQL Server concatenation methods have been enhanced in modern versions of SQL Server. SQL Server 2012 introduced the CONCAT() function. In SQL Server 2017 we get CONCAT_WS().

A common usage of concatenation, or joining column values together in a string, is combining a FirstName and LastName column into a FullName column.  Another common usage might be for creating an address column that pulls together building number, street, city and zip code.

Below I have provided an example of the three common ways to concatenate strings to create an address. These all produce the same results because there are no NULL values. They are just different ways of doing the same thing.

The first example is the CONCAT() method from SQL Server 2012, followed by the CONCAT_WS() function, which is CONCAT With Separator. The third method is the traditional method of combining strings with the
+” sign.

USE WideWorldImporters;
GO

SELECT CONCAT(DeliveryAddressLine2, ' ', C.CityName, ' ',DeliveryPostalCode) AS NewConcatMethod,

CONCAT_WS(' ', DeliveryAddressLine2, C.CityName,DeliveryPostalCode) AS CONCAT_WSMethod,

(DeliveryAddressLine2 + ' ' + C.CityName + ' ' + DeliveryPostalCode) AS OlderConcatMethod

FROM [WideWorldImporters].[Sales].[Customers] AS CUST
INNER JOIN [Application].[Cities] AS C ON CUST.DeliveryCityID = C.CityID;

NULLs And Concatenation

Before SQL Server 2012 the “+” sign was used to combine strings together into whatever form you needed. One disadvantage to this method is that if a value in the string to be concactenated is NULL then the result set returns a NULL.

USE AdventureWorks2014;
GO

SELECT TOP 5
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as MailingName
FROM Person.Person;

SELECT TOP 5
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ', LastName) as MailingName
FROM Person.Person;

In the top result set, you can see that whenever a NULL occurs the resulting MailingName column is also NULL. This makes some sense because NULL is “unknown” so you can’t really join an unknown value to other strings and get a predictable result.

However, with CONCAT(), columns with NULL values simply have those values ignored.

SQL Server CONCAT Function and NULL

What about concatenating numbers?

CREATE DATABASE TestDB;

CREATE TABLE IntValues
(Col1 INT);

INSERT INTO IntValues
VALUES(67),(45),(90)

SELECT CONCAT(67,45,90) AS Result
FROM dbo.IntAvlues;

SELECT CAST(67 as CHAR(2)) + CAST(45 as CHAR(2)) + CAST(90 as CHAR(2)) AS Result
FROM dbo.IntAvlues; 

Concatenating numbers works in the same way. The great thing here is that there is no need to do an explicit cast when using CONCAT().

SQL Server CONCAT_WS()

SQL Server 2017 brought us CONCAT_WS. With this new function, you specify the separator you want to use when building the string.  The value used as the separator goes first inside the parentheses followed by the column names that are to be joined together. These column names are separated by commas in a list.  Like CONCAT(), CONCAT_WS() eliminates NULLS from the result set, leaving you with the non-null values.

SELECT TOP 5 Title, FirstName, MiddleName, LastName, 
CONCAT_WS(' ',Title,FirstName,MiddleName,LastName) as MailingName 
FROM Person.Person;

SQL Server Concat_WS() Results

I think that the syntax with these new functions is easier to write and to read. These newer approaches reduce typing and having to switch between adding a “+” and ‘ ‘ in various places.  CONCAT_WS() reduces typing even more by only specifying the separator once at the beginning of the function.

Next Steps To Take

1. Check here for the MS Docs on CONCAT() and here for CONCAT_WS().
2. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.