| title | CONCAT_WS (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 07/24/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | article | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | f1375fd7-a2fd-48bf-922a-4f778f0deb1f | ||
| caps.latest.revision | 5 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ssvNxt-asdb-xxxx-xxx]
Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)
CONCAT_WS ( separator, argument1, argument1 [, argumentN]… ) separator
Is an expression of any character type (nvarchar, varchar, nchar, or char).
argument1, argument2, argumentN
Is an expression of any type.
String. The length and type depend on the input.
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).
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.
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 |
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,98052The 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;