Skip to main content

concat_ws

1. CONCAT_WS Function​

The CONCAT_WS function in H2 database is used to concatenate multiple strings into a single string, using a specified separator.

2. Syntax​

The syntax for the CONCAT_WS function in H2 database is as follows:

CONCAT_WS(separator, str1, str2, ...)

Arguments​

  • separator: The separator string that is used to combine the input strings. It should be a string literal or a column of type VARCHAR.
  • str1, str2, ...: The strings to be concatenated. These can be string literals, column names, or expressions that evaluate to strings.

Return​

  • The CONCAT_WS function returns a single string that is the result of concatenating the input strings with the specified separator.

3. Notes​

  • The CONCAT_WS function in H2 database differs from other databases in that the separator is always the first argument, whereas in some other databases it is the last argument.
  • If any of the input strings are NULL, the CONCAT_WS function will skip them and not include them in the output.
  • If all the input strings are NULL or if no strings are provided, the CONCAT_WS function will return an empty string.

4. Examples​

Here are a few examples demonstrating the usage of the CONCAT_WS function in H2 database:

Example 1 - Concatenating strings with a separator:​

SELECT CONCAT_WS(', ', 'John', 'Doe', 'Smith') AS full_name;

Output:

full_name
---------
John, Doe, Smith

Example 2 - Concatenating strings with a separator and skipping NULL values:​

SELECT CONCAT_WS(' - ', 'Apples', NULL, 'Oranges', NULL, 'Bananas') AS fruits;

Output:

fruits
----------------
Apples - Oranges - Bananas

Example 3 - Concatenating strings from columns with a separator:​

CREATE TABLE names (
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

INSERT INTO names VALUES ('John', 'Doe'), ('Jane', 'Smith'), ('Robert', 'Johnson');

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM names;

Output:

full_name
----------
John Doe
Jane Smith
Robert Johnson
  • concat - Concatenate strings without a separator
  • || - Concatenate strings using the double-pipe operator