CONCAT in SQL 2012

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.

SYNTAX:

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

Example  - CONCATENATION WITH CONCAT()

Declare @a Varchar(100)=Manish Kumar is ' Declare @b int=100 Declare @c varchar(200)=' times busy now.' Select CONCAT (@a, @b, @c) Go Returns: Manish Kumar is 100 times busy now.

In above example you can see that no data conversion is required for integer variable @b.

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.

CONCAT function only works with SQL Server 2012 and will work for later versions.

To read more http://msdn.microsoft.com/en-us/library/ms177561.aspx