TechNet
Export (0) Print
Expand All

CONCAT (Transact-SQL)

 

Updated: November 20, 2015

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2012)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
CONCAT ( string_value1, string_value2 [, string_valueN ] )  

string_value
A string value to concatenate to the other values.

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

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 typeOutput 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 casesvarchar(<= 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 SQL Server 2012 and above. For older linked servers, the CONCAT operation will be performed locally after the non-concatenated values are returned from the linked server.

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)  

C. Using CONCAT

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

Here is the result set.

Result  
-------------------------  
Happy Birthday 11/25  
  
(1 row(s) affected)  

Community Additions

ADD
Show:
© 2016 Microsoft