CONCAT (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL Endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. (To add a separating value during concatenation, see CONCAT_WS.)
Transact-SQL syntax conventions
Syntax
CONCAT ( string_value1, string_value2 [, string_valueN ] )
Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
string_value
A string value to concatenate to the other values. The CONCAT
function requires at least two string_value arguments, and no more than 254 string_value arguments.
Return types
string_value
A string value whose length and type depend on the input.
Remarks
CONCAT
takes a variable number of string arguments and concatenates (or joins) them into a single string. It requires a minimum of two input values; otherwise, CONCAT
will raise an error. CONCAT
implicitly converts all arguments to string types before concatenation. CONCAT
implicitly converts null values to empty strings. If CONCAT
receives arguments with all NULL values, it will return an empty string of type varchar(1). The implicit conversion to strings follows the existing rules for data type conversions. See CAST and CONVERT (Transact-SQL) for more information about data type conversions.
The return type depends on the type of the arguments. This table illustrates the mapping:
Input type | Output type and length |
---|---|
1. Any argument of a SQL-CLR system type a SQL-CLR UDT or nvarchar(max) |
nvarchar(max) |
2. Otherwise, any argument of type varbinary(max) or varchar(max) |
varchar(max), unless one of the parameters is an nvarchar of any length. In this case, CONCAT returns a result of type nvarchar(max). |
3. Otherwise, any argument of type nvarchar of at most 4000 characters ( nvarchar(<= 4000) ) |
nvarchar(<= 4000) |
4. In all other cases | varchar(<= 8000) (a varchar of at most 8000 characters) unless one of the parameters is an nvarchar of any length. In that case, CONCAT returns a result of type nvarchar(max). |
When CONCAT
receives nvarchar input arguments of length <= 4000 characters, or varchar input arguments of length <= 8000 characters, implicit conversions can affect the length of the result. Other data types have different lengths when implicitly converted to strings. For example, an int (14) has a string length of 12, while a float has a length of 32. Therefore, a concatenation of two integers returns a result with a length of no less than 24.
If none of the input arguments has a supported large object (LOB) type, then the return type truncates to 8000 characters in length, regardless of the return type. This truncation preserves space and supports plan generation efficiency.
The CONCAT function can be executed remotely on a linked server of version SQL Server 2012 (11.x) and above. For older linked servers, the CONCAT operation will happen locally, after the linked server returns the non-concatenated values.
Examples
A. Using CONCAT
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
Here is the result set.
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.
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)