PostgreSQL – CONCAT_WS Function

In PostgreSQL, the CONCAT_WS function is a powerful and versatile tool for concatenating strings with a specified separator. This function not only combines multiple string values but also efficiently handles NULL values, ignoring them in the concatenation process.

In this article, we will go deep into the syntax, practical examples, and outputs of the CONCAT_WS function, ensuring that we have a thorough understanding of how to utilize it effectively in our PostgreSQL queries.

What is the CONCAT_WS Function?

The CONCAT_WS function stands for “Concatenate With Separator.” It allows us to join multiple strings into a single string while inserting a specified separator between them. This is particularly useful when we want to create formatted strings or display data in a more readable format.

Syntax

CONCAT_WS(separator, string_1, string_2, ...);

Key Terms

• The separator is a string that is used to separate the resultant strings.
• The string_1, string_2, are strings that is to be converted.
• The CONCAT_WS function returns a combined string that is the combination of string_1, string_2, etc., separated by the separator.

Example 1: Concatenating Symbols

The following statement concatenates the symbols (ie, ^,+,-,/). The use of CONCAT_WS ensures that each part is clearly separated by the specified separator while ignoring any NULL values.

Query:

SELECT CONCAT_WS ('^^^', '+++ ', '---', '///');

Output

Explanation:

In this query, the function concatenates the strings ‘+++ ‘, ‘—‘, and ‘///‘ with the separator ‘^^^‘. The result is a single string where each of the original strings is separated by ‘^^^‘.

Example 2: Concatenating Multiple Strings

In this example, we will concatenate multiple strings, including text, to illustrate the flexibility of the CONCAT_WS function. Showing how CONCAT_WS not only combines the strings but also maintains clear separation between them with the specified separator. This example illustrates the function’s ability to handle various types of string data efficiently.

Query:

SELECT
 CONCAT_WS ('***', 'geeks ', 'for', 'geeks');

Output

PostgreSQL CONCAT_WS Function Example2

Explanation:

Here, the function takes three strings—’Geeks ‘, ‘for‘, and ‘Geeks‘—and concatenates them using ‘***’ as the separator. The resulting string clearly illustrates how the CONCAT_WS function allows for customized formatting.

Example 3: Handling NULL Values

One of the notable features of the CONCAT_WS function is its ability to handle NULL values gracefully. Let’s demonstrate this with an example:

Query:

SELECT CONCAT_WS(', ', 'Hello', NULL, 'World', NULL, '!');

Output

Hello, World, !

Explanation:

In this query, the function concatenates ‘Hello‘, NULL, ‘World‘, NULL, and ‘!’ using a comma and space as the separator. The NULL values are ignored, resulting in a clean output string.

Conclusion

The CONCAT_WS function in PostgreSQL is an invaluable tool for developers and database administrators alike, providing an efficient way to concatenate strings with customized separators. By understanding its syntax and functionality, we can effectively format your output data for improved readability and presentation. Whether we’re working with text strings, symbols, or managing NULL values, CONCAT_WS simplifies string manipulation in our PostgreSQL queries.