CONCAT_WS (Transact-SQL)CONCAT_WS (Transact-SQL)

適用於:是SQL Server (從 2017 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2017) yesAzure SQL Database yesAzure SQL Data Warehouse noParallel 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. 它使用第一個函數引數中指定的分隔符號來分隔這些串連的字串值。It separates those concatenated string values with the delimiter specified in the first function argument. (CONCAT_WS 指出 與分隔符號的串連。)(CONCAT_WS indicates concatenate with separator.)

語法Syntax

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

引數Arguments

separatorseparator
任何字元類型 (charncharnvarcharvarchar) 的運算式。An expression of any character type (char, nchar, nvarchar, or varchar).

引數1、引數2、引數Nargument1, argument2, argumentN
任意類型的運算式。An expression of any type.

傳回類型Return types

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

RemarksRemarks

CONCAT_WS 會採用可變數量的字串引數,並將其串連 (聯結) 成單一字串。CONCAT_WS takes a variable number of string arguments and concatenates (or joins) them into a single string. 它使用第一個函數引數中指定的分隔符號來分隔這些串連的字串值。It separates those concatenated string values with the delimiter specified in the first function argument. CONCAT_WS 需要分隔引數和至少兩個其他字串值引數。否則,CONCAT_WS 會引發錯誤。CONCAT_WS requires a separator argument and a minimum of two other string value arguments; otherwise, CONCAT_WS will raise an error. CONCAT_WS 會在串連之前將所有引數隱含地轉換成字串類型。CONCAT_WS implicitly converts all arguments to string types before concatenation.

隱含轉換成字串會遵循現有的資料類型轉換規則。The implicit conversion to strings follows the existing rules for data type conversions. 如需行為和資料類型轉換的詳細資訊,請參閱 CONCAT (Transact-SQL)See CONCAT (Transact-SQL) for more information about behavior and data type conversions.

NULL 值的處理方式Treatment of NULL values

CONCAT_WS 會忽略 SET CONCAT_NULL_YIELDS_NULL {ON|OFF} 字串。CONCAT_WS ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting.

如果 CONCAT_WS 收到全部都是 NULL 值的引數,則會傳回 varchar(1) 類型的空字串。If CONCAT_WS receives arguments with all NULL values, it will return an empty string of type varchar(1).

Null 值在串連期間會被 CONCAT_WS 忽略,而且不添加分隔符號。CONCAT_WS ignores null values during concatenation, and does not add the separator between null values. 因此,CONCAT_WS 可以乾淨地處理可能有「空白」值 - 例如,第二個地址欄位的字串串連。Therefore, CONCAT_WS can cleanly handle concatenation of strings that might have "blank" values - for example, a second address field. 如需詳細資訊,請參閱範例 B。See example B for more information.

如果案例牽涉到被分隔符號隔開的 Null 值,請考慮使用 ISNULL 函數。If a scenario involves null values separated by a delimiter, consider the ISNULL function. 如需詳細資訊,請參閱範例 C。See example C for more information.

範例Examples

A.A. 使用分隔符號串連值Concatenating values with separator

此範例會從 sys.databases 資料表中串連三個資料行,並用 - 隔開每個值。This example concatenates three columns from the sys.databases table, separating the values with a -.

SELECT CONCAT_WS( ' - ', database_id, recovery_model_desc, containment_desc) AS DatabaseInfo
FROM sys.databases;

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

DatabaseInfoDatabaseInfo
1 - SIMPLE - NONE1 - SIMPLE - NONE
2 - SIMPLE - NONE2 - SIMPLE - NONE
3 - FULL - NONE3 - FULL - NONE
4 - SIMPLE - NONE4 - SIMPLE - NONE

B.B. 跳過 NULL 值Skipping NULL values

此範例會忽略引數清單中的 NULL 值。This example ignores NULL values in the arguments list.

SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;

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

Address
------------   
1 Microsoft Way,Redmond,WA,98052

C.C. 從資料表產生 CSV 檔案Generating CSV file from table

此範例使用逗號 , 作為分隔符號值,並在結果集的資料行分隔值格式中新增歸位字元 char(13)This example uses a comma , as the separator value, and adds the carriage return character char(13) in the column separated values format of the result set.

SELECT 
STRING_AGG(CONCAT_WS( ',', database_id, recovery_model_desc, containment_desc), char(13)) AS DatabaseInfo
FROM sys.databases

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

DatabaseInfo
------------   
1,SIMPLE,NONE
2,SIMPLE,NONE
3,FULL,NONE 
4,SIMPLE,NONE 

CONCAT_WS 會忽略資料行中的 NULL 值。CONCAT_WS ignores NULL values in the columns. 使用 ISNULL 函數來包裝可為 Null 的資料行,並提供預設值。Wrap a nullable column with the ISNULL function, and provide a default value. 如需詳細資訊,請參閱此範例:See this example for more:

SELECT 
STRING_AGG(CONCAT_WS( ',', database_id, ISNULL(recovery_model_desc,''), ISNULL(containment_desc,'N/A')), char(13)) AS DatabaseInfo
FROM sys.databases;

另請參閱See also

CONCAT (Transact-SQL)CONCAT (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)