SQL Server CONCAT_WS() Function

Summary: in this tutorial, you will learn how to use the SQL Server CONCAT_WS() function to concatenate multiple strings into a single string with a specified separator.

Overview of SQL Server CONCAT_WS() function #

The CONCAT_WS() function allows you to concatenate multiple strings into a string string with a specified separator. CONCAT_WS() means concatenate with separator.

Here’s the syntax of the CONCAT_WS() function:

CONCAT_WS(separator,string1,string2,[...stringN]);Code language: SQL (Structured Query Language) (sql)

In this syntax:

separator: The delimiter that you use to separate the concatenated strings.
string1, string2, …: The strings that you want to concatenate.

The CONCAT_WS() function returns a single string formed by concatenating all the input strings with the specified separator.

Note that the CONCAT_WS() requires at least two input strings. This means that if pass zero or one input string argument, the CONCAT_WS() function will raise an error.

The CONCAT_WS() function treats NULL as an empty string of type VARCHAR(1). It also does not add the separator between NULLs. Therefore, the CONCAT_WS() function can cleanly join strings that may have blank values.

SQL Server CONCAT_WS() function examples #

Let’s take some examples of using the CONCAT_WS() function.

1) Using the CONCAT_WS() function to join literal strings with a separator #

The following example uses the CONCAT_WS() function to join two literal strings into one using a space:

SELECT 
  CONCAT_WS(' ', 'John', 'Doe') full_name;Code language: SQL (Structured Query Language) (sql)

Here is the output:

full_name
---------
John Doe

(1 row affected)

2) Using the CONCAT_WS() function with table data #

The following statement uses the CONCAT_WS() function to join values in the last_name and first_name columns of the sales.customers table using a comma (,) as the separator:

SELECT 
  first_name, 
  last_name, 
  CONCAT_WS(', ', last_name, first_name) full_name 
FROM 
  sales.customers 
ORDER BY 
  first_name, 
  last_name;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output: