CONCAT_WS (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with vNext)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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;

Here is the result set.

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;

Here is the result set.

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

Here is the result set.

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

String Functions (Transact-SQL)
CONCAT (Transact-SQL)