+= (String Concatenation) (Transact-SQL)

 

Updated: August 25, 2016

Applies To: Azure SQL Database, SQL Server (starting with 2008)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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'.

Topic link icon Transact-SQL Syntax Conventions

  
expression += expression  

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

Returns the data type that is defined for the variable.

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'  

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)

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

Community Additions

ADD
Show: