Skip to content

concat-ws-transact-sql.md

Latest commit

 

History

History
139 lines (109 loc) · 4.91 KB

concat-ws-transact-sql.md

File metadata and controls

139 lines (109 loc) · 4.91 KB

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.service: "" ms.component: "t-sql|functions" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "article" 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" ms.workload: "Active" monikerRange: "= azuresqldb-current || >= sql-server-2017 || = sqlallproducts-allversions"

CONCAT_WS (Transact-SQL)

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

Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)

Syntax

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

Arguments

separator
Is an expression of any character type (nvarchar, varchar, nchar, or char).

argument1, argument2, argumentN
Is an expression of any type.

Return types

String. The length and type depend on the input.

Remarks

CONCAT_WS takes a variable number of arguments and concatenates them into a single string using the first argument as separator. It requires a separator and a minimum of two arguments; otherwise, an error is raised. All arguments are implicitly converted to string types and are then concatenated.

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

Treatment of NULL values

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

If all the arguments are null, an empty string of type varchar(1) is returned.

Null values are ignored during concatenation, and does not add the separator. This facilitates the common scenario of concatenating strings which often have blank values, such as a second address field. See example B.

If your scenario requires null values to be included with a separator, see example C using the ISNULL function.

Examples

A. Concatenating values with separator

The following 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

The following 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

The following example uses a comma as the separator and adds the carriage return character to result in the column separated values format.

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 will ignore NULL values in the columns. If some of the columns are nullable, wrap it with ISNULL function and provide default value like in the following example:

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)