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

September 16, 2011

In SQL Server 2012, there are two new string functions being introduced, namely: CONCAT(), FORMAT(). In this blog post we are going to learn about String Function CONCAT(). 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 SingleString
SELECT CONCAT('One',1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT('One',2,NULL) AS SingleString
SELECT CONCAT('','','','') AS SingleString
SELECT 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 a 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]
GO
SELECT CONCAT([AddressID],' ',[AddressLine1],' ',
[AddressLine2],' ',[City],' ',
[StateProvinceID],' ',[PostalCode]) AS Address
FROM [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.
SQL SERVER 2012 - String Function CONCAT() - A Quick Introduction concat4

Now let us look under the hood in the execution plan. In the execution plan, we can see that the 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 data types columns to NVARCHAR.

SQL SERVER 2012 - String Function CONCAT() - A Quick Introduction concat3