Concatenation in SQL Server Denali Denali using CONCAT function

Microsoft introduced new function for string concatenation. CONCAT() is the function which is introduced in SQL Server Denali for concatenation which will take input and return the output as a single string.

Syntax

CONCAT (value1, value2,…..,value N)

You know we used + operator for concatenation in earlier versions of SQL Server. So now what’s the difference between using + operator and CONCAT function. There are actually two things you can notice when you utilize CONCAT function.

Lets do a test using CONCAT function and + operator. I’m going to create a table with product ID and product name and lets concatenate the data. Create the table using below script

USE tempdb
GO
CREATE TABLE test(product_id INT, product_name VARCHAR(50))
INSERT  test VALUES(1, 'Computer')
INSERT  test VALUES(1, NULL)

Testing on different Data type concatenation

SELECT product_id + ' - ' + product_name FROM test
WHERE product_id=1

The above query fails because both the columns are in different data type so it needs conversion before concatenation. So you need to convert all the data type to string before concatenating using + operator

SELECT CAST(product_id as VARCHAR(30)) + ' - ' + product_name FROM test
WHERE product_id=1

Lets do the same using CONCAT function, nothing conversion is required here, implicit conversion will automatically takes place

SELECT CONCAT(product_id,' - ',product_name) FROM test
WHERE product_id=1

Testing on NULL Values

SELECT CAST(product_id as VARCHAR(30)) + ' - ' + product_name FROM test
WHERE product_id=2

When I use the above query it will return only NULL value as product_id 2 has NULL value on product_name column. We need to use ISNULL function to take care of NULL values when we use + operator, so below modified query will work

SELECT CAST(product_id as VARCHAR(30)) + ' - ' +
ISNULL(product_name,'') FROM test
WHERE product_id=2

However when I use CONCAT function these things are not required, it will automatically convert NULL values to empty string.

SELECT CONCAT(product_id,' - ',product_name) FROM test
WHERE product_id=2

You can utilize this function from SQL Server Denali so you don’t need to worry about overhead while doing concatenation.