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.

+ operator

CONCAT function

Concatenated output will be NULL if any of the input data is NULL, so we used ISNULL function to check this NULL values are implicitly converted to empty string so concatenated output will not be NULL
If you have different data types in concatenation then you need to explicitly convert it to string else concatenation will fail Implicit conversion will take place irrespective of data type so no need to convert the data manually

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

concat_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

concat_2

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

concat_3

Testing on NULL Values

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

concat_4

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

concat_5

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

concat_6

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


Posted

in

by

Comments

2 responses to “Concatenation in SQL Server Denali Denali using CONCAT function”

  1. SQL-Articles » Concatenation in SQL Server Denali Denali using CONCAT function…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Brayan avatar

    Note: The same applies to GROUP_CONCAT. If you are coatncenating fields which might contain value of NULL, expect the unexpected. Using the COALESCE function will help, i.e.SELECT COALESCE(fieldname,”) + string

Leave a Reply

Your email address will not be published. Required fields are marked *