Introduction to PostgreSQL CONCAT_WS() function

The PostgreSQLCONCAT_WS() function allows you to concatenate multiple strings into a single string separated by a specified separator.

Here’s the basic syntax of theCONCAT_WS function:

CONCAT_WS(separator, string1, string2, string3, ...)

In this syntax:

• separator: Specify the separator that you want to separate the strings. Theseparator should not beNULL.
• string1,string2,string3, ..: These are strings that you want to concatenate. If any string is NULL, it is ignored by the function.

TheCONCAT_WS returns a single string that combines thestring1,string2,string3… separated by the separator.

If the separator isNULL, theCONCAT_WS will returnNULL.

In practice, you typically use theCONCAT_WS function to combine values from different columns with a custom separator.

PostgreSQL CONCAT_WS() function examples

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

1) Basic PostgreSQL CONCAT_WS() function example

The following example uses theCONCAT_WS() function to concatenate two strings with a space:

SELECT CONCAT_WS(' ','PostgreSQL','Tutorial') title;

Output:

title--------------------- PostgreSQL Tutorial(1 row)

In this example, we use theCONCAT_WS() function to concatenate the strings'PostgreSQL' and'Tutorial' with a space separator. The result string is'PostgreSQL Tutorial'.

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

We’ll use thecustomer table from thesample database:

The following example uses theCONCAT_WS() to concatenate values from thefirst_name andlast_name columns of thecustomer table using a space as a separator:

SELECT  CONCAT_WS(' ', first_name, last_name) full_nameFROM  customerORDER BY  first_name;

Output:

full_name----------------------- Aaron Selby Adam Gooch Adrian Clary Agnes Bishop...

The query returns a result set with a single columnfull_name containing the full names of all customers.

Summary

• Use theCONCAT_WS function to concatenate multiple strings into a single string separated by a specified separator.
• TheCONCAT_WS function skipsNULL values.

Last updated onJanuary 28, 2024

On this page

• Introduction to PostgreSQL CONCAT_WS() function
• PostgreSQL CONCAT_WS() function examples
• Summary