SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction

September 16, 2011 by pinaldave

In SQL Server Denali, there are two new string functions being introduced, namely:

CONCAT()
FORMAT()

Today we will quickly take a look at the CONCAT() function. CONCAT takes a minimum of two arguments to concatenate them, resulting to a single string.

Now let us look at these examples showing how CONCAT() works:

Example 1: CONCAT Function Usage

SELECT CONCAT(1, 2, 3, 4) AS SingleStringSELECT CONCAT('One',1, 1.1, GETDATE()) AS SingleStringSELECT CONCAT('One',2,NULL) AS SingleStringSELECT CONCAT('','','','') AS SingleStringSELECT CONCAT(NULL, NULL) AS SingleString

Now let us observe a few things based on the result above. Earlier when we had to concat strings, we used ‘+’ sign and always CAST/CONVERT any variable to string. It used to give us an error. However, when you look at this new one function, it automatically and implicitly CAST/CONVERT any datatype to integer and then CONCATs them together in a single string. NULL values are automatically converted to empty strings. If you notice that even the datetime fields are automatically converted to the string without any extra operations. Additionally, the return value from the CONCAT string could be of datatype VARCHAR(MAX).

Example 2: Usage of CONCAT with Table

USE [AdventureWorks2008R2]
GOSELECT CONCAT([AddressID],' ',[AddressLine1],' ',[AddressLine2],' ',[City],' ',[StateProvinceID],' ',[PostalCode]) AS AddressFROM [Person].[Address]
GO

In the following example, we see the result of the query listed above:

You can see how neatly and easily the strings are concatenated using this new function. It takes out lots of unnecessary code and makes it much simpler to execute.

Now let us look under the hood in the execution plan. In the execution plan we can see that CONCAT function is a scalar operation. When we look at the scalar operation using Properties, it shows that the CONVERT_IMPLICIT function is automatically called to convert non-nvarchar datatypes columns to NVARCHAR.

Overall, when I have to concat multiple values and data in the future, I am going to use the CONCATE() function.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)