Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

CONCAT() – SQL Server 2012

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

Comments

Posted by Samuel Vanga on 29 November 2011

I like the way it handles null and non varchar data types.

Using ISNULL and/or explictly converting int to varchar is not needed any more.

Format() is another new string function. I wrote about Format, and interestingly it's scheduled for later today.

Posted by poldi.rijke on 14 March 2012

Yes, the null handling is nice.

Leave a Comment

Please register or log in to leave a comment.