String Concatenation Operator

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. This is an example of concatenation:

SELECT ('abc' + 'def')

Here is the result set:

------

abcdef

(1 row(s) affected)

This query displays names of authors with California addresses under the Moniker column in last name, first name order, with a comma and space after the last name.

USE Northwind
GO

SELECT LastName + ', ' + FirstName AS Moniker

FROM Employees

WHERE Region = 'WA'

Here is the result set:

Moniker

-------------------------

Davolio, Nancy

Fuller, Andrew

Leverling, Janet

Peacock, Margaret

Callahan, Laura

(15 row(s) affected)

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

USE pubs

SELECT 'The due date is ' + CAST(pubdate AS varchar(128))

FROM titles

WHERE title_id = 'BU1032'

Here is the result set:

---------------------------------------

The due date is Jun 12 1991 12:00AM

(1 row(s) affected)

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

SELECT 'abc' + '' + 'def'

Here is the result set:

-------

abcdef

(1 row(s) affected)

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

See Also