SQL concatenate strings

In SQL, concatenating strings involves combining two or more string values into a single string. This can be useful in various scenarios, such as creating a full name by combining a first name and a last name, or constructing dynamic SQL queries. SQL provides several ways to concatenate strings, and the choice of method may depend on the version of SQL Server you are using. Here are some common methods for string concatenation in SQL Server:

Plus (+) Operator

The + operator can be used to concatenate strings in SQL Server. For example:

SELECT 
FirstName + ' ' + LastName AS FullName
FROM Employees;

In this example, the first name and last name are concatenated with a space in between to create a full name.

CONCAT Function

The CONCAT function is specifically designed for string concatenation. It takes two or more string values as arguments and concatenates them. For example:

SELECT 
CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;

This achieves the same result as the previous example but is more explicit in its purpose.

CONCAT_WS Function

The CONCAT_WS function is used to concatenate strings with a specified separator. This can be useful when you want to concatenate multiple strings with a specific delimiter. For example:

SELECT 
CONCAT_WS(', ', FirstName, LastName) AS FullName
FROM Employees;

In this example, the first name and last name are concatenated with a comma and a space in between.

Handling NULL Values

It’s important to note that the behavior of these methods can differ when dealing with NULL values. The CONCAT function treats NULL values as empty strings, while the + operator returns NULL if any operand is NULL.

SELECT CONCAT('Hello', NULL, 'World') AS Result; 
-- Result: HelloWorld

SELECT 'Hello' + NULL + 'World' AS Result;       
-- Result: NULL

To handle NULL values consistently, you might want to use the ISNULL or COALESCE function to replace NULL with an empty string or another default value.

SELECT CONCAT('Hello', ISNULL(NULL, ''), 'World') AS Result; 
-- Result: HelloWorld

SELECT 'Hello' + COALESCE(NULL, '') + 'World' AS Result;     
-- Result: HelloWorld

Choose the concatenation method that best suits your requirements and consider how each method handles NULL values based on your specific use case.