CONCAT function can be used to concatenate two or more strings. This function is available before in other databases like Oracle,MySQL etc. This feature will offer below advantages over standard concatenation operator(+)

• If any of the string is null, standard concatenation operator will return null as output. However, CONCAT() function will ignore null values while concatenating.
• As CONCAT() function is already available in other databases such as Oracle and MySql, while porting from one DB to other, this will gives more flexibility compared to standard operator.

The following example demonstrates a simple use case. One of the common mistakes people do is to forget to apply ISNULL when performing string concatenation. If one of the values happen to be NULL the result will be NULL too. The CONCAT function performs correct concatenation even if one of the values is NULL

DECLARE 
    @firstname VARCHAR(20) = 'Jacob', 
    @middlename VARCHAR(10) = NULL,
    @lastname VARCHAR(20) = 'Sebastian'

-- Without CONCAT function
SELECT @lastname + ' ' + @middlename + '' + @firstname
-- Returns NULL because @middlename is NULL

-- Using CONCAT function
SELECT CONCAT(@lastname, ' ', @middlename, ' ', @firstname) 
-- returns "Sebastian Jacob"

• Reference 1  [22 clicks]
• Reference 2  [20 clicks]
• Reference 3  [18 clicks]

Category :	TSQL Enhancements
Release :	All
Tags :	CONCAT,Concatenation,Denali,SQL Server 2012,SQL 11