This is one of the new string functions introduced in SQL Server 2012. This function provides an easy way for the developers to concatenate the string values. Let us first see the syntax of this method:

Syntax:

CONCAT( string1, string2 [,stringN])

Here the parameters: string1, string2, … ,stringN are the string values which will be concatenated. This method requires minimum two arguments and max 254 arguments.

Let us understand this new CONCAT function  with a simple example:

SELECT CONCAT('Basavaraj',' ','Biradar') Name

RESULT:
Name
—————–
Basavaraj Biradar

Some of the important features about this function are:
1) All arguments are implicitly converted to string types before concatenating them.

Let us see this with an example:

DECLARE @EmployeeId INT = 1
SELECT CONCAT(@EmployeeId,' ','Basavaraj') EmployeeIDAndName

RESULT:
EmployeeIDAndName
———————-
1 Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we were explicitly need to convert them to string and then concatenate. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with CAST/CONVERT function:

DECLARE @EmployeeId INT = 1
SELECT CAST(@EmployeeId AS VARCHAR) + ' ' 
             + 'Basavaraj' EmployeeIDAndName

2) Null values are implicitly converted to an empty string.

Let us see this with an example:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT CONCAT(@FirstName, ' ',@LastName) Name

RESULT:
Name
—————————————–
Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we know that if one of the values is NULL then concatenated value will be NULL. To avoid this we were needed to use ISNULL function to check if the value is null then consider it as empty string. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with ISNULL function:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT ISNULL(@FirstName,'') + ' ' 
       + ISNULL(@LastName,'') Name

QUIZ:
Whether the below query will return all the 8000 characters ‘B’ and 8000 characters ‘C’ in the result?

DECLARE @string1 AS VARCHAR(8000) = REPLICATE('B',8000),
 @string2 AS VARCHAR(8000) = REPLICATE('C',8000)
SELECT CONCAT(@string1,@string2)