SQL Server 2012 introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function and takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

 

Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.

 

SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN]) -- Between 2 and 254 values can be passed.

 

TEST 1 - CONCATENATION WITH CONCAT()

TEST 2 - CONCATENATION WITHOUT CONCAT()

Declare @a Varchar(100)='Microsoft SQL Server is now '

Declare @b int=22

Declare @c varchar(200)=' years old'

Select CONCAT (@a, @b, @c)

Go

 

Returns:

Microsoft SQL Server is now 22 Years old

 

Declare @a Varchar(100)='Microsoft SQL Server is now '

Declare @b int=22

Declare @c varchar(200)=' years old'

Select @a+Convert(varchar,(@b))+@c

Go;

 

Returns:

Microsoft SQL Server is now 22 Years old

 

 

In the above example, observe the use of CONCAT() function. There’s no data conversion being performed if you are using CONCAT function. However, in the second test, we are having to use data conversion function to convert Integer value to a string. 

 

The data returned by CONCAT function depends on the type of values passed. The below table shows the mapping of input and output types:

Input Value / Type

Output Value / Type

SQL-CLR (TYPES & UDT)

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(<=4000)

NVARCHAR(<=4000)

VARBINARY(MAX)

NVARCHAR(MAX)

ALL OTHER INPUT TYPES

VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

 

NOTE: CONCAT function only works with SQL Server 2012 and above versions. However, to make this work in older versions of SQL Server, you will need to create your own custom function that can take care of concatenating values and appropriately converting their data types.

DamirB-BlogSignature