concat-ws-transact-sql.md

---

title: "CONCAT_WS (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "07/24/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.component: "t-sql|functions" ms.reviewer: "" ms.suite: "sql" ms.technology: t-sql ms.tgt_pltfrm: "" ms.topic: conceptual f1_keywords:

• "CONCAT_WS"
• "CONCAT_WS_TSQL" dev_langs:
• "TSQL" helpviewer_keywords:
• "CONCAT_WS function" ms.assetid: f1375fd7-a2fd-48bf-922a-4f778f0deb1f caps.latest.revision: 5 author: edmacauley ms.author: edmaca manager: craigg monikerRange: "= azuresqldb-current || >= sql-server-2017 || = sqlallproducts-allversions"

---

CONCAT_WS (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2017-asdb-xxxx-xxx-md]

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 indicates concatenate with separator.)

Syntax

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… ) 

Arguments

separator
An expression of any character type (char', nchar', nvarchar, or varchar).

argument1, argument2, argumentN
An expression of any type.

Return types

A string value whose length and type depend on the input.

Remarks

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 requires a separator argument and a minimum of two other string value arguments; otherwise, CONCAT_WS will raise an error. CONCAT_WS implicitly converts all arguments to string types before concatenation.

The implicit conversion to strings follows the existing rules for data type conversions. See CONCAT (Transact-SQL) for more information about behavior and data type conversions.

Treatment of NULL values

CONCAT_WS ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting.

If CONCAT_WS receives arguments with all NULL values, it will return an empty string of type varchar(1).

CONCAT_WS ignores null values during concatenation, and does not add the separator between null values. Therefore, CONCAT_WS can cleanly handle concatenation of strings that might have "blank" values - for example, a second address field. See example B for more information.

If a scenario involves null values separated by a delimiter, consider the ISNULL function. See example C for more information.

Examples

A. Concatenating values with separator

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;

[!INCLUDEssResult_md]

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

B. Skipping NULL values

This example ignores NULL values in the arguments list.

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

[!INCLUDEssResult_md]

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

C. Generating CSV file from table

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

[!INCLUDEssResult_md]

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

CONCAT_WS ignores NULL values in the columns. 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)
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)