Simple CONCAT() Usage vs Manual String Building in SQL Server

When working with SQL Server, string concatenation is one of those everyday tasks that’s easy to take for granted. It can feel like second nature to reach for the trusty old + operator to piece together strings, but SQL Server also provides an alternative way to handle concatenations. Yes, I’m referring to the CONCAT() function.

And there’s a subtle difference between the two approaches that might sway you towards using one or the other.

Let’s compare these two approaches to building strings in SQL Server.

Manual String Building with the Concatenation Operator (`+`)

The traditional way to join strings in SQL Server is to use the + operator. For example, imagine a simple table Customers with FirstName, LastName, and City columns:

-- Create the Customers table
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    City NVARCHAR(50)
);

-- Insert some sample rows
INSERT INTO Customers (FirstName, LastName, City)
VALUES
    ('Carla', 'Nguyen', 'Seattle'),
    ('Tico', 'Torres', 'Chicago'),
    ('Stacey', 'Singh', NULL),
    ('Derek', 'Olsen', 'New York'),
    ('Elena', 'Martinez', NULL);

If we wanted to format these into a readable sentence, we might do something like:

SELECT FirstName + ' ' + LastName + ' lives in ' + City AS CustomerInfo
FROM Customers;

Result:

CustomerInfo                 
-----------------------------
Carla Nguyen lives in Seattle
Tico Torres lives in Chicago 
null                         
Derek Olsen lives in New York
null

While this works well for most of the rows, if any of the values are NULL, the whole expression collapses into NULL. That means if a customer’s city isn’t provided, the entire sentence becomes empty instead of just skipping the city part. To avoid that, we can wrap columns in ISNULL() or COALESCE():

SELECT FirstName + ' ' + LastName + ' lives in ' + COALESCE(City, 'Unknown') AS CustomerInfo
FROM Customers;

Result:

CustomerInfo                   
-------------------------------
Carla Nguyen lives in Seattle  
Tico Torres lives in Chicago   
Stacey Singh lives in Unknown  
Derek Olsen lives in New York  
Elena Martinez lives in Unknown

This time we were still able to get the first and last names of everyone, even when the city was unknown.

Another Approach with `CONCAT()`

SQL Server’s CONCAT() function works slightly differently. It takes multiple arguments and automatically treats NULL values as empty strings. If there’s a NULL value it doesn’t collapse the whole string into NULL.

Here’s the same query using CONCAT():

SELECT CONCAT(FirstName, ' ', LastName, ' lives in ', City) AS CustomerInfo
FROM Customers;

Result:

CustomerInfo                 
-----------------------------
Carla Nguyen lives in Seattle
Tico Torres lives in Chicago 
Stacey Singh lives in        
Derek Olsen lives in New York
Elena Martinez lives in

In this case, instead of getting NULL for the whole row, we get an empty string where the NULL value is. This might be all you need, depending on your requirements. But we still have the option of using COALESCE() in order to provide some placeholder text, like “unknown”:

SELECT CONCAT(FirstName, ' ', LastName, ' lives in ', COALESCE(City, 'Unknown')) AS CustomerInfo
FROM Customers;

Result:

CustomerInfo                   
-------------------------------
Carla Nguyen lives in Seattle  
Tico Torres lives in Chicago   
Stacey Singh lives in Unknown  
Derek Olsen lives in New York  
Elena Martinez lives in Unknown

Summary

Here’s a quick summary of the difference between the two approaches:

NULL handling: CONCAT() replaces NULL values with an empty string while preserving the non-NULL parts of the concatenated string. Using the concatenation operator replaces the whole result with NULL in such scenarios.
Readability: Queries with CONCAT() can be more concise, especially with lots of concatenations.

So there’s nothing inherently wrong with using the + operator. It’s simple and has been around forever. However, if you’re working with columns that might contain NULL, or you just want to make your queries more readable, CONCAT() may suit your needs better.

Manual String Building with the Concatenation Operator (+)

Another Approach with CONCAT()

Summary

Manual String Building with the Concatenation Operator (`+`)

Another Approach with `CONCAT()`