+ (String Concatenation) (Transact-SQL)
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).
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
Returns the data type of the argument with the highest precedence. For more information, see Data Type Precedence (Transact-SQL).
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. When you work with strings with a null value, the result of the concatenation depends on the session settings. Just like 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 that is 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 (Transact-SQL).
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 does not occur.
A. Using string concatenation
The following example creates a single column under the column heading Name from multiple character columns, with the last 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 last name, and then by the first name.
USE AdventureWorks2012; GO SELECT (LastName + ', ' + FirstName) AS Name FROM Person.Person ORDER BY LastName ASC, FirstName ASC;
B. Combining numeric and date data types
The following example uses the CONVERT function to concatenate numeric and date data types.
USE AdventureWorks2012; GO 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
(1 row(s) affected)
C. Using multiple string concatenation
The following example concatenates multiple strings to form one long string to display the last name and the first initial of the vice presidents at Adventure Works Cycles. A comma is added after the last name and a period after the first initial.
USE AdventureWorks2012; GO 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.
Duffy, T. Vice President of Engineering
Hamilton, J. Vice President of Production
Welcker, B. Vice President of Sales
(3 row(s) affected)