CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments.

Syntax:

Concat([String1],[String2]..[StringN]); 

DECLARE @FName VARCHAR(50),@LName VARCHAR(50);

SET @FName='Rakesh' ;

SET @LName='Kalluri';

SELECT CONCAT(@FName,' ', @LName) as [FullName];

Output: Rakesh Kalluri

--Before SQL 2012 Versions we can use like this way

DECLARE @FName VARCHAR(50),@LName VARCHAR(50);

SET @FName='Rakesh' ;

SET @LName='Kalluri';

SELECT @FName+' '+@LName as [FullName];

Output: Rakesh Kalluri

--But Before SQL 2012 Versions there are 2 problems when we contacting.

--1.Need to replace NULL Value to Empty.if we directly concat NULL Value the Entire result also NULL.

--Before 2012:

DECLARE @FName VARCHAR(50),@LName VARCHAR(50);

SET @FName='Rakesh' ;

SELECT @FName+' '+@LName as [FullName];

Output: NULL

--From 2012 (Null values are implicitly converted to empty)

DECLARE @FName VARCHAR(50),@LName VARCHAR(50);

SET @FName='Rakesh' ;

SELECT CONCAT(@FName,' ', @LName) as [FullName];

Output: Rakesh

--2.Need to Convert String Datatype

--Before 2012:

DECLARE @FName VARCHAR(50),@Id INT;

SET @FName='Rakesh' ;

SET @Id=1

SELECT @FName+' '+@Id as [Details];

Msg 245, Level 16, State 1, Line 5

Conversion failed when converting the varchar value 'Rakesh ' to data type int.

--Problem Solving Before 2012 Versions

SELECT @FName+' '+CAST(@Id AS VARCHAR(50)) as [Details];

Output: Rakesh 1

--From 2012

DECLARE @FName VARCHAR(50),@Id INT;

SET @FName='Rakesh' ;

SET @Id=1

SELECT CONCAT(@FName,' ', @Id) as [Details];

Output: Rakesh 1