Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Chris Di LulloSr. IT Pro Marketing Manager Twitter | LinkedIn Pierre Roman Twitter | LinkedIn Mitch Garvis Twitter | LinkedIn Anthony Bartolo Twitter | LinkedIn
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.
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)
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)
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.