With SQL Server 2012 a new string function is born – CONCAT(). The output is a string, as the result of the concatenation of two or more strings – if the input is another type than string a conversion will be done implicit. The CONCAT() function takes between 2 and 254 parameters.

Here is how the syntax looks:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Read the full BOL documentation here. Let’s have a look at code use cases.

/* The Simple one */

SELECT CONCAT('AGF','Vinder','Guld','I','2012') as GeniiiusOutput

Output: AGFVinderGuldI2012

/* Handling non varchar datatypes */

DECLARE @text1 varchar(10) = 'AGF'
DECLARE @text2 varchar(10) = 'Vinder'
DECLARE @text3 varchar(10) = 'Guld'
DECLARE @text4 varchar(10) = 'I'
DECLARE @Num1 int = 2012

SELECT CONCAT(@text1, @text2, @text3, @text4, @Num1) as GeniiiusOutput

Output: AGFVinderGuldI2012

/* Handling null values */

DECLARE @text1 varchar(10) = 'AGF'
DECLARE @text2 varchar(10) = 'Vinder'
DECLARE @text3 varchar(10) = null
DECLARE @text4 varchar(10) = 'I'
DECLARE @Num1 int = 2012

SELECT CONCAT(@text1, @text2, @text3, @text4, @Num1) as GeniiiusOutput

Output: AGFVinderI2012

This is for sure a feature that I have been missing, and already now it has become my new best friend.

@Geniiiuscom