+ (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 expression must 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.
DECLARE @mybin1 binary(5), @mybin2 binary(5)
SET @mybin1 = 0xFF
SET @mybin2 = 0xA5
-- No CONVERT or CAST function is necessary because this example
-- concatenates two binary strings.
SELECT @mybin1 + @mybin2
-- A CONVERT or CAST function is necessary because this example
-- concatenates two binary strings plus a space.
SELECT CONVERT(varchar(5), @mybin1) + ' '
+ CONVERT(varchar(5), @mybin2)
-- Here is the same conversion using CAST
SELECT CAST(@mybin1 AS varchar(5)) + ' '
+ CAST(@mybin2 AS varchar(5))
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
Here is the result set:
Name
--------------------------------------------------------------
Bennet, Abraham
Blotchet-Halls, Reginald
Carson, Cheryl
DeFrance, Michel
del Castillo, Innes
Dull, Ann
Green, Marjorie
Greene, Morningstar
Gringlesby, Burt
Hunter, Sheryl
Karsen, Livia
Locksley, Charlene
MacFeather, Stearns
McBadden, Heather
O'Leary, Michael
Panteley, Sylvia
Ringer, Albert
Ringer, Anne
Smith, Meander
Straight, Dean
Stringer, Dirk
White, Johnson
Yokomoto, Akiko
(23 row(s) affected)
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.
USE pubs
SELECT (au_lname + ',' + SPACE(1) + SUBSTRING(au_fname, 1, 1) + '.') AS Name
FROM authors
WHERE state = 'CA'
ORDER BY au_lname ASC, au_fname ASC
Here is the result set:
Name
--------------------------------------------
Bennet, A.
Carson, C.
Dull, A.
Green, M.
Gringlesby, B.
Hunter, S.
Karsen, L.
Locksley, C.
MacFeather, S.
McBadden, H.
O'Leary, M.
Straight, D.
Stringer, D.
White, J.
Yokomoto, A.
(15 row(s) affected)