Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

+ (String Concatenation)

SQL Server 2000

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)

See Also

CAST and CONVERT

Data Type Conversion

Data Types

Expressions

Functions

Operators

SELECT

SET

Setting Database Options

sp_dboption

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft