+ (String concatenation) (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
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). For example SELECT 'book'+'case';
returns bookcase
.
Transact-SQL syntax conventions
Syntax
expression + expression
Arguments
expression
Any valid expression of any one 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.
Return types
Returns the data type of the argument with the highest precedence. For more information, see Data type precedence.
Remarks
If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation doesn't occur.
Zero-length strings and characters
The +
string concatenation operator behaves differently when it works with an empty, zero-length string than when it works with NULL
, or unknown values. A zero-length character string can be specified as two single quotation marks without any characters inside the quotation marks. A zero-length binary string can be specified as 0x
without any byte values specified in the hexadecimal constant. Concatenating a zero-length string always concatenates the two specified strings.
Concatenation of NULL values
When you work with strings with a NULL
value, the result of the concatenation depends on the session settings. As with arithmetic operations that are performed on NULL
values, when a NULL
value is added to a known value the result is typically an unknown value, a string concatenation operation performed with a NULL
value, should also produce a NULL
result.
However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL
for the current session. For more information, see SET CONCAT_NULL_YIELDS_NULL.
Use of CAST and CONVERT when necessary
An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings.
The following examples show when CONVERT
, or CAST
, must be used with binary concatenation and when CONVERT
, or CAST
, doesn't have to be used.
In this example, no CONVERT
or CAST
function is required because this example concatenates two binary strings.
DECLARE @mybin1 VARBINARY(5), @mybin2 VARBINARY(5);
SET @mybin1 = 0xFF;
SET @mybin2 = 0xA5;
-- No CONVERT or CAST function is required because this example
-- concatenates two binary strings.
SELECT @mybin1 + @mybin2;
In this example, a CONVERT
or CAST
function is required because this example concatenates two binary strings plus a space.
DECLARE @mybin1 VARBINARY(5), @mybin2 VARBINARY(5);
SET @mybin1 = 0xFF;
SET @mybin2 = 0xA5;
-- A CONVERT or CAST function is required 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));
Examples
The Transact-SQL code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A. Use string concatenation
The following example creates a single column under the column heading Name
from multiple character columns, with the family name of the person followed by a comma, a single space, and then the first name of the person. The result set is in ascending, alphabetical order by the family name, and then by the first name.
SELECT (LastName + ', ' + FirstName) AS Name
FROM Person.Person
ORDER BY LastName ASC, FirstName ASC;
B. Combine numeric and date data types
The following example uses the CONVERT
function to concatenate numeric and date data types.
SELECT 'The order is due on ' + CONVERT(VARCHAR(12), DueDate, 101)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 50001;
GO
Here is the result set.
------------------------------------------------
The order is due on 04/23/2007
C. Use multiple string concatenation
The following example concatenates multiple strings to form one long string to display the family name and the first initial of the vice presidents at Adventure Works Cycles. A comma is added after the family name and a period after the first initial.
SELECT (LastName + ',' + SPACE(1) + SUBSTRING(FirstName, 1, 1) + '.') AS Name, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle LIKE 'Vice%'
ORDER BY LastName ASC;
GO
Here is the result set.
Name Title
------------- ---------------`
Duffy, T. Vice President of Engineering
Hamilton, J. Vice President of Production
Welcker, B. Vice President of Sales
D. Use large strings in concatenation
The following example concatenates multiple strings to form one long string and then tries to compute the length of the final string. The final length of result set is 16,000, because expression evaluation starts from left that is, @x
+ @z
+ @y
=> (@x + @z
) + @y
. In this case, the result of (@x
+ @z
) is truncated at 8,000 bytes, and then @y
is added to the result set, which makes the final string length 16,000. Since @y
is a large value type string, truncation doesn't occur.
DECLARE @x VARCHAR(8000) = REPLICATE('x', 8000)
DECLARE @y VARCHAR(max) = REPLICATE('y', 8000)
DECLARE @z VARCHAR(8000) = REPLICATE('z',8000)
SET @y = @x + @z + @y
-- The result of following select is 16000
SELECT LEN(@y) AS y
GO
Here is the result set.
y
-------
16000
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
The Transact-SQL code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
E. Use multiple string concatenation
The following example concatenates multiple strings to form one long string to display the family name and the first initial of the vice presidents within a sample database. A comma is added after the family name and a period after the first initial.
SELECT (LastName + ', ' + SUBSTRING(FirstName, 1, 1) + '.') AS Name, Title
FROM DimEmployee
WHERE Title LIKE '%Vice Pres%'
ORDER BY LastName ASC;
Here is the result set.
Name Title
------------- ---------------
Duffy, T. Vice President of Engineering
Hamilton, J. Vice President of Production
Welcker, B. Vice President of Sales
Related content
- || (String concatenation) (Transact-SQL)
- += (String Concatenation Assignment) (Transact-SQL)
- ALTER DATABASE (Transact-SQL)
- CAST and CONVERT (Transact-SQL)
- Data type conversion (Database Engine)
- Data types (Transact-SQL)
- Expressions (Transact-SQL)
- Built-in Functions (Transact-SQL)
- Operators (Transact-SQL)
- SELECT (Transact-SQL)
- SET Statements (Transact-SQL)