+ (String Concatenation) (T-SQL)

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.

---

Note 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, alphabetic 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)

See Also