CONCAT() IN SQL SERVER 2012

CONCAT() IN SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT() which allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values. Good thing about the function is that it 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. 

EXAMPLE:

DECLARE @FirstName NVARCHAR(10) = N'हरि' 
       ,@LastNameVARCHAR(10) = 'Sharma'
       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'
       ,@Age INT = 30
       ,@Delimiter VARCHAR(2) = SPACE(2)
SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell,@Delimiter, @Age)

OUTPUT:

　

You might have observed that there’s no data conversion being performed in the above example.

The data returned by CONCAT function depends on the type of valued passed. Below are some of the mapping 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 later versions.