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:

3) Using the CONCAT_WS() function with NULL

The following statement demonstrates how the CONCAT_WS() function handles input strings that have NULL values:

SELECT 
  CONCAT_WS(',', 1, 2, NULL, NULL, 3);Code language: SQL (Structured Query Language) (sql)

The output is as follows:

result
----------------------------------------
1,2,3

(1 row affected)

The output indicates that the CONCAT_WS() function ignores NULL and doesn’t add the separator between NULL values.

The following example concatenates customer data to format customer’s addresses. If a customer does not have a phone number, the CONCAT_WS() function ignores it:

SELECT 
    CONCAT_WS
    (
        CHAR(13), 
        CONCAT(first_name, ' ', last_name), 
        phone, 
        CONCAT(city, ' ', state), 
        zip_code,
        '---'
    ) customer_address
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;Code language: SQL (Structured Query Language) (sql)

This picture illustrates the partial output:

Note that you must change the result of the query from the grid to text to see the output in the above format:

4) Using the CONCAT_WS() function to generate CSV data

This statement uses a comma (,) as the separator and concatenates values in first_name, last_name, and email column to generate a CSV file:

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

The partial output is as follows:

Summary

• Use the SQL Server CONCAT_WS() function to concatenate multiple strings into a single string with a specified separator.