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.
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.
Overall, when I have to concat multiple values and data in the future, I am going to use the CONCATE() function.
Watch a quick video relevent to this subject:
Reference: Pinal Dave (http://blog.SQLAuthority.com)