SQL Server CONCAT() Function

 The CONCAT() function in SQL Server is used to concatenate two or more strings into a single string. It automatically handles NULL values by treating them as empty strings, which makes it a safer and more convenient alternative to using the + operator for string concatenation.

Syntax:

CONCAT(string1, string2, ..., stringN)

Parameters:

• string1, string2, ..., stringN: The strings or expressions you want to concatenate. You can specify constants, variables, or columns.

Key Points:

1. It can accept two or more arguments.
2. Automatically treats NULL values as empty strings (''), so no need to explicitly handle NULL.
3. If any non-string argument is included, SQL Server implicitly converts it to a string.

---

Example Usage:

Example 1: Basic Concatenation

SELECT CONCAT('Hello', ' ', 'World') AS Result;

Output:

Result
------
Hello World

Example 2: Concatenating Columns

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

Output:

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

Example 3: Handling NULL Values

SELECT CONCAT('FirstName: ', NULL, ', LastName: ', 'Doe') AS Result;

Output:

Result
-----------------
FirstName: , LastName: Doe

Example 4: Mixed Data Types

SELECT CONCAT('Order ID: ', OrderID, ', Total: $', TotalAmount) AS OrderSummary
FROM Orders;

Output:

OrderSummary
-------------------------
Order ID: 101, Total: $250.50
Order ID: 102, Total: $125.00

---

Notes:

• SQL Server Version: The CONCAT() function is available starting from SQL Server 2012.
• For older versions, you can use the + operator but must handle NULL values explicitly using ISNULL() or COALESCE().

Let me know if you'd like additional examples or further clarification!