concat-transact-sql.md

title	CONCAT (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	07/24/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.service
ms.component	t-sql|functions
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	CONCAT CONCAT_TSQL
dev_langs	TSQL
helpviewer_keywords	CONCAT function
ms.assetid	fce5a8d4-283b-4c47-95e5-4946402550d5
caps.latest.revision	22
author	edmacauley
ms.author	edmaca
manager	craigg
ms.workload	Active

CONCAT (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2012-all-md]

Returns a string that is the result of concatenating two or more string values. (To add a separating value during concatenation, see CONCAT_WS.)

Transact-SQL Syntax Conventions

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 implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

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.

The CONCAT function can be executed remotely on a linked server which is version [!INCLUDEssSQL11] and above. For older linked servers, the CONCAT operation will be performed locally after the non-concatenated values are returned from the linked server.

Examples

A. Using CONCAT

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

[!INCLUDEssResult]

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;  

[!INCLUDEssResult]

Result  
------------------  
NameLastname  
  
(1 row(s) affected)  

See also

CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)
REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)
String Functions (Transact-SQL)