CONCAT() string function in SQL Server 2012

Introduction:

CONCAT() is string function is used to concatenating two or more string values. CONCAT() funciton is introduced with SQL Server 2012 version. For earlier version( Before SQL Server 2012) plus sign(+) operator is used to to concatenate strings. Now using CONCAT function, it's easy for developers to concatenate string values.

Synax:

CONCAT ( string1, string2,string3 [, stringN ] )

Input Parameters:

string1, string2,string3,stringN - input string values to concatenate.

Returns:

String value from the input strings.

Now we will see different features available with CONCAT function and also how to achieve the same features with SQL Server earlier versions (2008R2, 2008, 2005, 2000).

Feature 1: Simple Example - Concatenating strings

SQL2012 Version: CONCAT() function is used to concatenate.

SELECT CONCAT('DotNetMirror',' ','Reflects Your Knowledge')

Output: DotNetMirror Reflects Your Knowledge

Earlier Versions: plus sign(+) operator is used to concatenate.

SELECT 'DotNetMirror' + ' ' + 'Reflects Your Knowledge'

Output: DotNetMirror Reflects Your Knowledge

Feature 2: Implicitly converted to String

SQL2012 Version: In the below example using CONCAT function, int value 2014 will be automatically converted to string and appended with string value. But in case of earlier versions it throws error.

DECLARE @Year INT = 2014
SELECT CONCAT(@Year,' ','DotNetMirror')

Output: 2014 DotNetMirror

Earlier Versions:

DECLARE @Year INT = 2014
SELECT @Year + ' ' + 'DotNetMirror'

Output: Error as "Conversion failed when converting the varchar value 'DotNetMirror' to data type int." In order to resolve the error convert int value to VARCHAR type using cast function.

DECLARE @Year INT = 2014
select CAST(@Year AS VARCHAR(5)) + ' ' + 'DotNetMirror'

Output: 2014 DotNetMirror

Feature 3: Implicitly converted Null values to Empty String

SQL2012 Version: CONCAT function implicitly converts NULL values to Empty value but in case of earlier versions it returns NULL if you have any one null value in concatenation.

DECLARE @Name VARCHAR(20) = 'DotNetMirror', 
DECLARE @NullVal VARCHAR(20) = NULL
SELECT CONCAT(@Name, ' ',@NullVal)

Ouput: DotNetMirror

Earlier Versions:

DECLARE @Name VARCHAR(20) = 'DotNetMirror', 
DECLARE @NullVal VARCHAR(20) = NULL

Output: NULL

To Overcome NULL issue, use ISNULL function to check whether the value is null or not. If null take empty value otherwise take original value.

select @Name + ' ' +ISNULL(@NullVal,'')

Feature 4: Concatenating Table Nullable column values

CONCAT function is very useful in case of concatenating table nullable column values. It ignores null values in the column's data and concatenates remaining data. But in case of earlier versions if you have one NULL value column data then entire concatenation returns NULL. In the below example you can observe the difference.

INSERT INTO #student VALUES( 'A', 'B', 'C' );
INSERT INTO #student VALUES( 'D', NULL, 'E' );
SELECT CONCAT( FristName, MiddleName, LastName ) AS FullName
FROM #student;

SQL2012 Version:

SELECT CONCAT( FristName, MiddleName, LastName ) AS FullName FROM #student;

Output:

ABC

DE (Ignored NULL)

Earlier Versions:

SELECT FristName +  MiddleName + LastName AS FullName FROM #student;

Output:

ABC

NULL (because one of middle name data contains null value)