DotNet Mirror
  DNM facebook   DNM Google+   DNM Twitter   

CONCAT() string function in SQL Server 2012

By Ashok Nalam on 10 Feb 2014 | Category: SQL Server | Tagged: string tsql SQL Server 2012 
Article shows how to use CONCAT() function which is introduced with SQL Server 2012 version and also covers different features of it.
  Discuss Add Comment   |  
Add rating Rate this resource   

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)
  Discuss Add Comment    
Add rating Rate this resource   
About the Contributor
Member Since : 10 Dec 2012
Member Points (Level) : 8926  (Professional)
Location : INDIA
Home Page : http://dotnetmirror.com
About : I am admin of this site.
Rate this resource
 
Add your Comment
Name Email WebSite
Captcha Refresh


Comments (0)
No comments found, click here to add comment.