Use the CONCAT function in SQL Server to join two or more strings into one unique string.
The SQL Server CONCAT function takes up to 255 input arguments, which can be any valid expression as long as they are not of the text data type. If any argument is NULL, CONCAT will treat it as an empty string. For instance, let’s insert some data into the Products table, and then use CONCAT to join Name and Description.
Table of Contents
1. Introduction to the T-SQL CONCAT function
The CONCAT function in SQL Server allows developers to concatenate strings. It takes as arguments the strings to concatenated. Here is the basic syntax: CONCAT(string1, string2, ..., stringN). It’s important to mention that if any of these strings is null, CONCAT will treat it as an empty string. First, let’s start with an example using the following table structure and data:
CREATE TABLE Products(
ProductID int PRIMARY KEY,
Name varchar(100),
Description varchar(500),
Price decimal(10,2),
Category varchar(100)
);
INSERT INTO Products(ProductID, Name, Description, Price, Category)
VALUES (1, 'Refrigerator', 'Large with double doors', 1500.00, 'Electronics'),
(2, 'Microwave', 'Compact with quick heating feature', 200.00, 'Electronics'),
(3, 'Television', '55 inches with 4K resolution', 1200.00, 'Electronics');
2. Simple SQL Server CONCAT example
Let’s start with a simple example. If you want to combine the Name and Description of products into a single string, you can use the CONCAT function like this:
SELECT CONCAT(Name, ' - ', Description) AS ProductDetails FROM Products;
The above query will result in a new column, ProductDetails, containing the Name and Description of each product separated by a hyphen.
3. Concatenate Multiple Strings in SQL Server
With this third SQL code example, you can pass more than two strings to the CONCAT function. Let’s add the price to the product details:
SELECT CONCAT(Name, ' - ', Description, ' - ', Price) AS ProductDetails FROM Products;
Now, the ProductDetails column will also contain the Price of each product.
4. Using Null Values in SQL Server concatenation
As mentioned earlier in this tutorial, if one of the strings passed to CONCAT is null, it will be treated as an empty string. Here is an example to illustrate this particular case:
UPDATE Products SET Description = NULL WHERE ProductID = 3; SELECT CONCAT(Name, ' - ', Description, ' - ', Price) AS ProductDetails FROM Products;
Even though the description for the third product is null, the CONCAT function will not result in a null value. It will treat the null description as an empty string.
5.Concatenate text and Non-String Values
Moreover, the CONCAT text function can accept non-string arguments. If a non-string value is passed, it will be implicitly converted to a string. Here is an example of how the ProductID is transformed to text:
SELECT CONCAT('Product ID: ', ProductID, ', Name: ', Name) AS ProductDetails
FROM Products;
In this query, the ProductID, which is an integer, is concatenated with some strings to create the ProductDetails column.
6. Using the CONCAT_WS text function
In SQL Server, you can use the CONCAT_WS (Concatenate With Separator) function to concatenate strings with an explicitly specified separator. This can be very useful when dealing with multiple strings, like different columns.
SELECT CONCAT_WS(' - ', Name, Description, Price) AS ProductDetails
FROM Products;
In this example, CONCAT_WS adds a hyphen between each of the concatenated strings. If any of the strings is null, it will be ignored, and the hyphen will not be added. In this other T-SQL tutorial, here’s how to manage SQL Server dates using built-in functions.
Now let’s dive into some more advance concatenation techniques used in real life databases projects.
7. Using CONCAT with Conditional Statements in SQL Server
In this section, let’s see how to use the CONCAT function along with CASE WHEN statement to conditionally concatenate values. Let’s consider an example where we only want to include the price in the ProductDetails if it is above 1000.
SELECT CONCAT(Name, ' - ', Description,
CASE
WHEN Price > 1000 THEN CONCAT(' - ', Price)
ELSE ''
END) AS ProductDetails
FROM Products;
In the above example, the price will only be concatenated to the ProductDetails if it is above 1000. If not, an empty string is concatenated. The overall behaviour is similar in SQL Server and Python.
8. Using CONCAT and JOIN Operations
The CONCAT function can also be used with JOIN operations to merge data from different tables. Assume we have another table, Categories, containing CategoryID and CategoryName. Let’s add some category data and demonstrate this:
CREATE TABLE Categories(
CategoryID int PRIMARY KEY,
CategoryName varchar(100)
);
INSERT INTO Categories(CategoryID, CategoryName)
VALUES (1, 'Electronics'),
(2, 'Appliances'),
(3, 'Furniture');
UPDATE Products
SET Category = 1
WHERE ProductID IN (1, 2, 3);
SELECT CONCAT(P.Name, ' - ', P.Description, ' - ', C.CategoryName) AS ProductDetails
FROM Products AS P
INNER JOIN Categories AS C
ON P.Category = C.CategoryID;
In this query, we concatenate product details with the category name, pulled from the Categories table.
9. CONCAT and Aggregate Functions in SQL Server
In other usage, CONCAT can be used along with aggregate functions to provide more meaningful outputs. Suppose you want to get the total price of all products and output a statement, you could easily use some code like this one below. As a result, this will simply output a single row with a message stating the total price of all products.
SELECT CONCAT('The total price of all products is: ', SUM(Price)) AS TotalPrice
FROM Products;
10. Using the CONCAT function in Stored Procedures
Last but not least, the CONCAT function is not limited to SELECT queries and can be used inside stored procedures to build dynamic SQL or return information. Let’s create a stored procedure that receives a ProductID and returns a string with the product details:
CREATE PROCEDURE GetProductDetails @ProductID int AS
BEGIN
DECLARE @Result varchar(1000);
SELECT @Result = CONCAT(Name, ' - ', Description, ' - ', Price)
FROM Products
WHERE ProductID = @ProductID;
SELECT @Result AS ProductDetails;
END
To call this stored procedure, you can use:
EXEC GetProductDetails @ProductID = 1;
The stored procedure will return a single string with the product details.
Conclusion on SQL CONCAT Function
SQL Server’s CONCAT function offers a robust method to fuse string values together. You can apply it in multiple situations, ranging from straightforward concatenation tasks to intricate database operations. Grasping its fundamental usage and the ways to integrate it with other SQL functionalities will enhance your data management and manipulation abilities.
Be the first to comment