Concatenating Strings in SQL Server using 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. 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.

Contact function syntax:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])

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.

Test 1 – Concatenating with CONCAT function

Declare @a Varchar(100)='Microsoft SQL Server is now '
Declare @b int=32
Declare @c varchar(200)=' years old'
Select CONCAT (@a, @b, @c)
Go
 
--Above script returns the following value:
Microsoft SQL Server is now 32 Years old

Test 2 – Let’s take similar example but with data type conversion:

Declare @a Varchar(100)='Microsoft SQL Server is now '
Declare @b int=32
Declare @c varchar(200)=' years old'
 
Select @a+Convert(varchar,(@b))+@c 
 
--Please note the convert function usage above
 
Returns:
Microsoft SQL Server is now 32 Years old

In the above examples, observe the use of CONCAT() function. There’s 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 / 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 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.