A lil’ concat lesson

To concatenate two strings in pre-Denali versions, you have “always” been able to do so with the plus sign (‘+’).

For example add the string ‘Another string’ to the right of string ‘String’:

SELECT‘String’+‘Another string’

 –Result:

 (1 row(s) affected)

 StringAnother string

But – If you try to concatenate a varchar string with an integer with help of the ‘+’ sign (concatenation) you get an error message, example: I want this to be String10 as a result.

 SELECT‘String’+ 10

 –Result:

 .Net SqlClient Data Provider: Msg 245,Level 16,State 1, Line 1

Conversion failed when converting the varchar value ‘String’to data typeint.

But then again, if the first part happens to be able to be implicitly converted to an integer, you would actually succeed but you would not get the wanted result since the plus sign is also the plus operator:

 SELECT‘1’+ 10

 –Result:

 (1 row(s) affected)

 11

This can be overcome for example by casting the integer to the varchar string datatype:

 SELECT‘String’+CAST(10 asvarchar)

 –Result:

 (1 row(s) affected)

 String10

And the other version now also correctly concatenated to the string 110:

 SELECT‘1’+CAST(10 asvarchar)

 –Result:

 (1 row(s) affected)

 110

Now to the news part – In upcoming Denali version of SQL Server, the string and integer values could, instead of using the plus sign method, be concatenated with the new keyword Concat:

 SelectConcat(‘String’,10)As [Concat]

 –Result:

 (1 row(s) affected)

 String10

 The other exampel:

SelectConcat(1,10)As [Concat]

 –Result:

 (1 row(s) affected)

 110

Here is the syntax for the CONCAT command:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

The max amount of values to be concatenated is 254 (you should be able to do most of your work within that limit, I guess). If one or more arguments are of NULL value that part is represented by an empty string. In case all of the arguments is NULL you still get a string back, a varchar(1) empty string.

If you use the old plus sign concatenation, the result would be a NULL if one of the parts are of NULL value.

All non string datatypes are implicitly converted to a string datatype before being concatenated.

If you need any help regarding current, previous or upcoming versions of SQL Server, please contact one of our SQL Server consultants.

/Jonas Bergström