CONCATINATION AND +

+ (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).

Syntax

expression + expression

Arguments

expression

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

An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings. The following example shows when CONVERT, or CAST, must be used with binary concatenation and when CONVERT, or CAST, does not have to be used.

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

-- 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))

Result Types

Returns the data type of the argument with the highest precedence.

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.

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.

Examples

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.

Name               Title

-------------      ---------------

Duffy, T.          Vice President of Engineering

Hamilton, J.       Vice President of Production

Welcker, B.        Vice President of Sales

(3 row(s) affected)

 CONCAT (Transact-SQL) - 2012

Returns a string that is the result of concatenating two or more string values.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Arguments

string_value

A string value to concatenate to the other values.

Return Types

String, the length and type of which depend on the input.

Remarks

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised.

All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string.

If all the arguments are null, an empty string of type varchar(1) is returned.

The return type depends on the type of the arguments.

The following table illustrates the mapping.

Input type	Output type and length
If any argument is a SQL-CLR system type, a SQL-CLR UDT, or nvarchar(max)	nvarchar(max)
Otherwise, if any argument is varbinary(max) or varchar(max)	varchar(max) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).
Otherwise, if any argument is nvarchar(<= 4000)	nvarchar (<= 4000)
Otherwise, in all other cases	varchar (<= 8000)unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).

When the arguments are <= 4000 for nvarchar, or <= 8000 for varchar, implicit conversions can affect the length of the result.

Other data types have different lengths when they are implicitly converted to strings.

For example, an int (14) has a string length of 12, while a float has a length of 32. Thus the result of concatenating two integers has a length of no less than 24.

If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

This function is capable of being remoted to SQL Server 2012 servers and above. It will not be remoted to servers that have a version below SQL Server 2012.

Examples

A. Using CONCAT

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

Here is the result set.

Copy

Result

-------------------------

Happy Birthday 11/25

(1 row(s) affected)

B. Using CONCAT with NULL values

CREATE TABLE #temp (

    emp_name nvarchar(200) NOT NULL,

    emp_middlename nvarchar(200) NULL,

    emp_lastname nvarchar(200) NOT NULL

);

INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );

SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result

FROM #temp;

Here is the result set.

Copy

Result

------------------

NameLastname

(1 row(s) affected)