How to concatenate in SQL Server
Mastering Concatenation in SQL Server: Techniques and Best Practices

Learn the various methods for concatenating strings and columns in SQL Server, from basic operators to advanced functions, and understand their performance implications.
Concatenation is a fundamental operation in SQL Server, allowing you to combine multiple strings, column values, or literals into a single string. This is incredibly useful for generating reports, creating formatted output, or constructing dynamic queries. SQL Server provides several ways to achieve concatenation, each with its own nuances and performance characteristics. This article will explore the most common methods, discuss their differences, and provide best practices for effective string manipulation.
The Basic Concatenation Operator: +
The +
operator is the most straightforward and commonly used method for concatenating strings in SQL Server. It can combine two or more string expressions, including column values, literal strings, and variables. However, it's crucial to understand how NULL
values are handled when using this operator.
SELECT 'Hello' + ' ' + 'World' AS Greeting;
SELECT 'First Name: ' + FirstName + ', Last Name: ' + LastName AS FullName
FROM Employees;
-- Handling NULLs with '+':
SELECT 'Value1: ' + 'Value2' + NULL + 'Value3' AS ResultWithNull;
Examples of using the +
operator for string concatenation.
+
operator is that if any part of the concatenation is NULL
, the entire result will be NULL
. This behavior can lead to unexpected results if not explicitly handled.The CONCAT()
Function: A Modern Approach
Introduced in SQL Server 2012, the CONCAT()
function offers a more robust and user-friendly way to concatenate strings, especially when dealing with NULL
values. Unlike the +
operator, CONCAT()
implicitly converts NULL
values to an empty string, preventing the entire result from becoming NULL
.
SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
SELECT CONCAT('First Name: ', FirstName, ', Last Name: ', LastName) AS FullName
FROM Employees;
-- Handling NULLs with CONCAT():
SELECT CONCAT('Value1: ', 'Value2', NULL, 'Value3') AS ResultWithNull;
Examples of using the CONCAT()
function for string concatenation.
flowchart TD A[Start Concatenation] --> B{Input Values} B --> C{Check for NULLs} C -- '+' Operator --> D{Any NULL?} D -- Yes --> E[Result is NULL] D -- No --> F[Combine Strings] C -- CONCAT() Function --> G{Convert NULLs to Empty String} G --> F F --> H[Output Concatenated String] E --> H
Comparison of NULL handling between +
operator and CONCAT()
function.
NULL
handling, especially in newer SQL Server versions, CONCAT()
is generally preferred over the +
operator for string concatenation.The CONCAT_WS()
Function: Concatenate With Separator
SQL Server 2017 introduced CONCAT_WS()
(Concatenate With Separator), which is particularly useful when you need to combine multiple string values with a specified separator. This function also handles NULL
values by skipping them, rather than converting them to empty strings or returning NULL
for the entire result.
SELECT CONCAT_WS(', ', 'John', 'Doe', 'Software Engineer') AS EmployeeDetails;
-- CONCAT_WS() with NULLs:
SELECT CONCAT_WS(' - ', 'Part1', NULL, 'Part3', 'Part4') AS ResultWithNulls;
-- Example with address components:
SELECT CONCAT_WS(', ', StreetAddress, City, State, ZipCode) AS FullAddress
FROM Customers;
Examples of using the CONCAT_WS()
function with and without NULLs.
CONCAT_WS()
is the separator, followed by the strings to be concatenated. It's ideal for scenarios like creating comma-separated lists or formatted address strings where you want to omit missing components.