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. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server. This eliminates 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.

Let’s look at an example using Concat string function: In this example, we will use three strings stored in different variables and then concatenate the strings using Concat function.

Note: Concat function can concatenate up to 254 values.

Let’s take similar example but with data type conversion

 

In the above examples, observe the use of CONCAT() function. Theres no data conversion being performed in the 1st test. However, in the 2nd, we are using 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 / TypeOutput Value / Type
SQL-CLR (TYPES & UDT)NVARCHAR(MAX)
NVARCHAR(MAX)NVARCHAR(MAX)
NVARCHAR(<=4000)NVARCHAR(<=4000)
VARBINARY(MAX)NVARCHAR(MAX)
ALL OTHER INPUT TYPESVARCHAR(<=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 all the versions above SQL12. 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.