CONCAT()
CONCAT(): It requires minimum of 2 input strings as parameter and concatenates or appends one after the other, into one string. It gives an error if less than two parameters are given. Values which are given as Null are converted implicitly into an empty string.
Syntax of CONCAT():
CONCAT (string_value1, string_value2 [, string_valueN ] )
Example of CONCAT():
SELECT CONCAT ('Born ','on ', 01,'/','26') AS Output_Result;
The output will be: Born on 01/26.
For complete post go to SQL Server 2012 Enhancements.