+ (String Concatenation)

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Syntax

expression+ expression

Arguments

expression

Is any valid Microsoft® SQL Server™ expression of any of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expressionmust be able to be implicitly converted to the data type of the other expression.

An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings. The following example shows when CONVERT (or CAST) must be used with binary concatenation and when CONVERT (or CAST) does not need to be used.

Result Types

Returns the data type of the argument with the highest precedence. For more information, see Data Type Precedence.

Remarks

When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is 'string'.

Examples

A. Use string concatenation

This example creates a single column (under the column heading Name) from multiple character columns, with the author's last name followed by a comma, a single space, and then the author's first name. The result set is in ascending, alphabetical order by the author's last name, and then by the author's first name.

USE pubs
SELECT (au_lname + ', ' + au_fname) AS Name
FROM authors
ORDER BY au_lname ASC, au_fname ASC

B. Combine numeric and date data types

This example uses the CAST function to concatenate numeric and date data types.

USE pubs
SELECT 'The order date is ' + CAST(ord_date AS varchar(30))
FROM sales
WHERE ord_num = 'A2976'
ORDER BY ord_num

Here is the result set:

------------------------------------------------
The order date is May 24 1993 12:00AM
(1 row(s) affected)

C. Use multiple string concatenation

This example concatenates multiple strings to form one long string. To display the last name and the first initial of each author living in the state of California, a comma is placed after the last name and a period after the first initial.