CONCAT (Transact-SQL)CONCAT (Transact-SQL)

適用於: 是SQL Server (從 2012 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2012) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

此函式會傳回透過以端對端方式串連 (或聯結) 兩個以上字串值所產生的字串。This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. (若要在串連期間新增分隔值,請參閱CONCAT_WS。)(To add a separating value during concatenation, see CONCAT_WS.)

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )  

引數Arguments

string_valuestring_value
要串連至其他值的字串值。A string value to concatenate to the other values. CONCAT 函式需要至少兩個 string_value 引數,而且不能超過 254 個 string_value 引數。The CONCAT function requires at least two string_value arguments, and no more than 254 string_value arguments.

傳回類型Return types

string_valuestring_value
長度和類型取決於輸入的字串值。A string value whose length and type depend on the input.

RemarksRemarks

CONCAT 會採用可變數量的字串引數,並將其串連 (聯結) 成單一字串。CONCAT takes a variable number of string arguments and concatenates (or joins) them into a single string. 它至少需要兩個輸入值,否則 CONCAT 會引發錯誤。It requires a minimum of two input values; otherwise, CONCAT will raise an error. CONCAT 會在串連之前將所有引數隱含地轉換成字串類型。CONCAT implicitly converts all arguments to string types before concatenation. CONCAT 會將 Null 值隱含地轉換成空字串。CONCAT implicitly converts null values to empty strings. 如果 CONCAT 收到全部都是 NULL 值的引數,則會傳回 varchar(1) 類型的空字串。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. 如需資料類型轉換的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)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.下者的任何引數:1. Any argument of

SQL-CLR 系統類型a SQL-CLR system type

SQL-CLR UDTa SQL-CLR UDT

中的多個or

nvarchar(max)
nvarchar(max)nvarchar(max)
2.否則,是下列類型的任何參數:2. Otherwise, any argument of type

varbinary(max)varbinary(max)

中的多個or

varchar(max)varchar(max)
varchar(max) ,除非其中一個參數是任何長度的 nvarcharvarchar(max), unless one of the parameters is an nvarchar of any length. 在此情況下,CONCAT 會傳回 nvarchar(max) 類型的結果。In this case, CONCAT returns a result of type nvarchar(max).
3.否則為 nvarchar 類型且最多 4000 個字元的任何引數3. Otherwise, any argument of type nvarchar of at most 4000 characters

(nvarchar(<= 4000))( nvarchar(<= 4000) )
nvarchar(<= 4000)nvarchar(<= 4000)
4.在所有其他情況下4. In all other cases varchar(<= 8000) (最多 8000 個字元的 varchar),除非其中一個參數是任何長度的 nvarchar。varchar(<= 8000) (a varchar of at most 8000 characters) unless one of the parameters is an nvarchar of any length. 在該情況下,CONCAT 會傳回 nvarchar(max) 類型的結果。In that case, CONCAT returns a result of type nvarchar(max).

CONCAT 收到長度 <= 4000 個字元的 nvarchar 輸入引數或長度 <= 8000 個字元的 varchar 輸入引數時,隱含轉換可能會影響結果長度。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. 例如,int (14) 的字串長度為 12,而 float的長度為 32。For example, an int (14) has a string length of 12, while a float has a length of 32. 因此,串連兩個整數會傳回長度不超過 24 的結果。Therefore, a concatenation of two integers returns a result with a length of no less than 24.

如果輸入引數沒有支援的大型物件 (LOB) 類型,則無論傳回類型為何,傳回類型的長度都會截斷成 8000 個字元。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.

CONCAT 函式可以在 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 版及更高版本的連結伺服器上,以遠端方式執行。The CONCAT function can be executed remotely on a linked server of version SQL Server 2012 (11.x)SQL Server 2012 (11.x) and above. 對於較舊的連結伺服器,會在連結伺服器傳回非串連值之後,於本機執行 CONCAT 作業。For older linked servers, the CONCAT operation will happen locally, after the linked server returns the non-concatenated values.

範例Examples

A.A. 使用 CONCATUsing CONCAT

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

以下為結果集:Here is the result set.

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

B.B. 使用 CONCAT 搭配 NULL 值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) CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)REPLACE (Transact-SQL)
REVERSE (Transact-SQL)REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)TRANSLATE (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)