SQL Server 2012 - CONCAT() - String function

CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values.

The CONCAT function requires a minimum of two input values, else the concatenation fails.

SYNTAX: - 

                   CONCAT (string_value1, string_value2 [,string_valueN])

In previous versions of SQL Server, we had plus symbol " +  "  to concatenate values.

Even in SQL Server 2012, we can use plus symbol " +  "  to concatenate values.

CONCAT( )  function overcomes data type conversion problem while concatenating values of different data types, in a simple way by implicitly coverting all arguments to string types before concatenating the inputs.

Null values are implicitly converted to an empty string. 

If all the arguments are null, an empty string of type varchar(1) is returned. 

Examples for CONCAT( ) function:

USE AdventureWorks2012
--Concatenating using plus symbol "+" 
SELECT JobTitle + '|' + HireDate FROM  [HumanResources].[Employee]
GO

Msg 402, Level 16, State 1, Line 1
The data types nvarchar and date are incompatible in the add operator.

--Concatenating using CONCAT  ( ) function
SELECT CONCAT(JobTitle,'|',HireDate) FROM  [HumanResources].[Employee]
GO

SELECT CONCAT(JobTitle,NULL,HireDate) FROM  [HumanResources].[Employee]
GO

SELECT CONCAT(NULL,NULL,NULL) 
GO

SELECT CONCAT(1,'A','!') 
GO

PRINT CONCAT('CONCAT() function ' , 'can also be used with PRINT')
GO

Reference - http://msdn.microsoft.com/en-us/library/hh231515.aspx

See Also:

• My Posts on T-SQL