CONCAT_WS (Transact-SQL)

Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

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, argument2 [, argumentN]... )

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

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

argument1, argument2, argumentN An expression of any type. The CONCAT_WS function requires at least two arguments, and no more than 254 arguments.

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;

Here is the result set.

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;

Here is the result set.

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

C. Generating CSV-formatted data 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

Here is the result set.

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)