string-concatenation-equal-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	helpviewer_keywords	dev_langs	monikerRange
+= String concatenation	Concatenate two strings and set the string to the result of the operation.	rwestMSFT	randolphwest	12/07/2016	sql	t-sql	reference	ignite-2024	concatenate strings string concatenation += (concatenate operator)	TSQL	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

+= (String Concatenation Assignment) (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Concatenates two strings and sets the string to the result of the operation. For example, if a variable @x equals 'Adventure', then @x += 'Works' takes the original value of @x, adds 'Works' to the string, and sets @x to that new value 'AdventureWorks'.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

expression += expression  

Arguments

expression
Is any valid expression of any of the character data types.

Result Types

Returns the data type that is defined for the variable.

Remarks

SET @v1 += 'expression' is equivalent to SET @v1 = @v1 + ('expression'). Also, SET @v1 = @v2 + @v3 + @v4 is equivalent to SET @v1 = (@v2 + @v3) + @v4.

The += operator cannot be used without a variable. For example, the following code will cause an error:

SELECT 'Adventure' += 'Works'  

Examples

A. Concatenation using += operator

The following example concatenates using the += operator.

DECLARE @v1 VARCHAR(40);  
SET @v1 = 'This is the original.';  
SET @v1 += ' More text.';  
PRINT @v1;  

[!INCLUDEssResult]

This is the original. More text.

B. Order of evaluation while concatenating using += operator

The following example concatenates multiple strings to form one long string and then tries to compute the length of the final string. This example demonstrates the evaluation order and truncation rules, while using the concatenation operator.

DECLARE @x VARCHAR(4000) = REPLICATE('x', 4000)
DECLARE @z VARCHAR(8000) = REPLICATE('z',8000)
DECLARE @y VARCHAR(max);
 
SET @y = '';
SET @y += @x + @z;
SELECT LEN(@y) AS Y; -- 8000
 
SET @y = '';
SET @y = @y + @x + @z;
SELECT LEN(@y) AS Y; -- 12000
 
SET @y = '';
SET @y = @y +(@x + @z);
SELECT LEN(@y) AS Y; -- 8000
-- or
SET @y = '';
SET @y = @x + @z + @y;
SELECT LEN(@y) AS Y; -- 8000
GO

[!INCLUDEssResult]

Y       
------- 
8000 
 
(1 row(s) affected) 
 
   
Y       
------- 
12000 
 
(1 row(s) affected) 

Y       
------- 
8000 
 
(1 row(s) affected) 
 
Y       
------- 
8000 
 
(1 row(s) affected)

See Also

Operators (Transact-SQL)
+= (Add Assignment) (Transact-SQL)
+ (String Concatenation) (Transact-SQL)