To join one or more strings in SQL Server, we can use the SQL Concat() function. String concatenation is a common task in SQL Server when working with textual data. The CONCAT() function allows users to merge multiple string values into one. However, when dealing with NULL values, SQL Server’s behavior can lead to unexpected results. So the COALESCE() function with CONCAT() helps in handling these NULL values effectively by converting them into a default value.
Table of Contents:
What is the CONCAT() Function in SQL Server?
The SQL CONCAT() will use the Coalesce function to handle NULL values effectively by considering them as an empty string. It can join two or more strings into a single string but it does not concatenate values across multiple rows. This function is useful for generating readable sentences from database records.
Examples of CONCAT() Function in SQL Server
The CONCAT() function can be used to convert multiple rows by using a concatenation of words in the query.
Example 1: To get the employee’s name and department ID
------Create table name employees
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Department NVARCHAR(50),
EmployeeName NVARCHAR(100)
);
-----Insert the values into the employees table
INSERT INTO Employees (ID, Department, EmployeeName)
VALUES
(1, 'HR', 'Kani'),
(2, 'Content Writer', 'Vasu'),
(3, 'HR', 'Ravi'),
(4, 'HR', 'Sri'),
(5, 'Content Writer', 'Rathore');
------Query to concatenate the employee table with the department
SELECT EmployeeName, Department,
CONCAT(COALESCE(EmployeeName, ''), ' works in ', COALESCE(Department, ''), ' department.') AS EmployeeDetails
FROM Employees;
Output:
Explanation: A fixed string, ‘works in,’ is added to make the sentence more readable.
Example 2: To get the first name, middle name, and last name of the employee with their department.
---Create the table name employee with middle name
CREATE TABLE EmployeesWithMiddleName (
ID INT PRIMARY KEY,
FirstName NVARCHAR(50),
MiddleName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50)
);
----Insert the value into the table
INSERT INTO EmployeesWithMiddleName (ID, FirstName, MiddleName, LastName, Department)
VALUES
(1, 'John', NULL, 'Doe', 'IT'),
(2, 'Emma', 'Marie', 'Watson', 'Finance'),
(3, 'Robert', NULL, 'Brown', 'HR'),
(4, 'Sophia', 'Anne', 'Miller', NULL),
(5, NULL, NULL, 'Taylor', 'Marketing');
---Query to display the concatenated table value
SELECT
LEFT(COALESCE(FirstName, ''), 10) AS FirstName,
LEFT(COALESCE(MiddleName, ''), 10) AS MiddleName,
LEFT(COALESCE(LastName, ''), 10) AS LastName,
LEFT(COALESCE(Department, 'Unknown'), 10) AS Department,
LEFT(CONCAT(COALESCE(FirstName, ''), ' ',
COALESCE(MiddleName + ' ', ''),
COALESCE(LastName, ''), ' works in ', COALESCE(Department, 'Unknown'), ' dept.'),50) AS EmployeeDetails
FROM EmployeesWithMiddleName;
Output:
Explanation: You can see that in the output the middle name of some names is not there. That will be considered as a NULL value. The CONCAT() will ignore the NULL value instead of leaving a space. For wherever the department name is NULL, it will show unknown instead of ignored. It uses the “works in” concatenated word to join the strings together.
Key Points
- It handles NULL values efficiently.
- The concat() function generates the data into a readable format. This makes the data more accessible.
- It concatenates multiple rows into a single text string.
Conclusion
The COALESCE() with CONCAT() in SQL Server is a very efficient way of handling NULL values for better performance and readability. It can be used to format any data from multiple rows into a single text string. It is very useful while handling dynamic data, log details reports, etc.
Some Other Methods to Concatenate Text from Multiple Rows into a Single Text String
- Using STRING_AGG() in SQL Server
- Using SQL Server STUFF() Function
- Using FOR XML PATH clause in SQL Server
- Using COALESCE() Function in SQL Server