SQL Server Concat With +

 In SQL Server, the + operator can be used to concatenate (combine) strings. Here are some important details and examples:

Basic Syntax

SELECT 'Hello' + ' ' + 'World';

Output:
Hello World

Concatenating Columns

You can use the + operator to combine the values of columns in a table:

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

Handling NULL Values

One key aspect of using + for concatenation in SQL Server is how it handles NULL values:

• If any value in the concatenation is NULL, the entire result becomes NULL.

Example:

SELECT 'Hello' + NULL + 'World';

Output:
NULL

Solution for NULL Values

To avoid this issue, use the ISNULL() or COALESCE() function to replace NULL values with an empty string:

SELECT 'Hello' + ISNULL(NULL, '') + 'World';

Output:
HelloWorld

Alternatively, starting from SQL Server 2012, you can use the CONCAT() function, which automatically handles NULL values as empty strings:

SELECT CONCAT('Hello', NULL, 'World');

Output:
HelloWorld

Example with Table Data

CREATE TABLE Students (FirstName NVARCHAR(50), LastName NVARCHAR(50));
INSERT INTO Students VALUES ('John', 'Doe'), ('Jane', NULL);

SELECT FirstName + ' ' + ISNULL(LastName, '') AS FullName
FROM Students;

Output:

FullName
------------
John Doe
Jane

Using the + operator for concatenation is simple and effective but requires attention to how NULL values are handled. For modern SQL Server versions, CONCAT() is often preferred for ease and reliability.