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
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.
Leave a Reply