Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

String Concatenation Operator (Database Engine)

The string concatenation operator is the plus sign (+). You can combine, or concatenate, two or more character strings into a single character string. You can also concatenate binary strings. The following is an example of concatenation:

SELECT ('abc' + 'def');

Here is the result set. 



(1 row(s) affected)

The following query displays names of the first four contacts under the Moniker column in last name, first name order, with a comma and space after the last name.

USE AdventureWorks2008R2;
SELECT LastName + ', ' + FirstName AS Moniker 
FROM Person.Person
WHERE BusinessEntityID < 5;

Here is the result set. 



Achong, Gustavo

Abel, Catherine

Abercrombie, Kim

Acevedo, Humberto

(4 row(s) affected)

Other data types, such as datetime and smalldatetime, must be converted to character strings by using the CAST conversion function before they can be concatenated with a string.

USE AdventureWorks2008R2;
SELECT 'The due date is ' + CAST(DueDate AS varchar(128))
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 43659;

Here is the result set. 


The due date is Jul 13 2005 12:00AM

(1 row(s) affected)

The empty string ('') is evaluated as a single space:

SELECT 'abc' + '' + 'def';

Here is the result set. 



(1 row(s) affected)


Whether an empty string ('') is interpreted as a single blank character or as an empty character is determined by the compatibility level setting of the database. . For this example, if the compatibility level is 65, empty literals are treated as a single blank.

When the input strings both have the same collation, the output string has the same collation as the inputs. When the input strings have different collations, the rules of collation precedence determine the collation of the output string. You can also assign a specific collation by using the COLLATE clause.

Community Additions

© 2015 Microsoft