CONCAT Function

Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation more neatly. For example, the below script and screenshot shows usage of the “+” operator for string values concatenation and its result.

SELECT ‘One’ + ‘,’ + ‘Two’ + ‘,’ + ‘Three’
GO
SELECT FirstName + ‘,’ + LastName FROM [Person].[Person]
GO

CONCAT is a new T-SQL function, which accepts two or more parameter values separated by comma, introduced in SQL Server 2012. All passed-in parameter values are concatenated to a single string and are returned back.

The script below demonstrates the usage of the CONCAT function to concatenate two or more string values. As the CONCAT function has been designed for string concatenation, it converts all the non-string input values to string data type and performs concatenation on that.

SELECT CONCAT(‘One’, ‘,’, ‘Two’, ‘,’, ‘Three’, ‘,’, 12345)
GO
SELECT CONCAT(BusinessEntityID, ‘,’, FirstName, ‘,’, LastName) FROM [Person].[Person]
GO