+= (String Concatenation Assignment) (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
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'.
Transact-SQL syntax conventions
Syntax
expression += expression
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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;
Here is the result set.
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
Here is the result set.
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)
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for