How the CONCAT_WS() function works in Mariadb?

The CONCAT_WS() function is a string function that concatenates two or more strings with a specified separator.

Posted on

The CONCAT_WS() function is a string function that concatenates two or more strings with a specified separator. The name of the function stands for concatenate with separator. The function returns a string that is the result of joining the arguments with the separator. The separator is added between the strings to be concatenated. The separator can be a string, as well as a binary or a blob value.

Syntax

The syntax of the CONCAT_WS() function is as follows:

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

The function takes at least two arguments. The first argument is the separator that is used to join the strings. The rest of the arguments are the strings that are concatenated. The function skips the NULL values in the arguments list. If the separator is NULL, the function returns NULL. If all the arguments are NULL, the function returns an empty string.

Examples

Example 1: Concatenating strings with a comma separator

The following example uses the CONCAT_WS() function to concatenate three strings with a comma as the separator.

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

The output is:

+---------------------------------------+
| CONCAT_WS(',', 'Hello', 'World', '!') |
+---------------------------------------+
| Hello,World,!                         |
+---------------------------------------+

Example 2: Concatenating strings with a space separator

The following example uses the CONCAT_WS() function to concatenate four strings with a space as the separator.

SELECT CONCAT_WS(' ', 'This', 'is', 'a', 'sentence.');

The output is:

+------------------------------------------------+
| CONCAT_WS(' ', 'This', 'is', 'a', 'sentence.') |
+------------------------------------------------+
| This is a sentence.                            |
+------------------------------------------------+

Example 3: Concatenating strings with a NULL separator

The following example uses the CONCAT_WS() function to concatenate two strings with a NULL as the separator.

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

The output is:

+-----------------------------------+
| CONCAT_WS(NULL, 'Hello', 'World') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+

Example 4: Concatenating strings with a NULL value in the arguments list

The following example uses the CONCAT_WS() function to concatenate three strings with a comma as the separator. One of the strings is NULL.

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

The output is:

+----------------------------------------+
| CONCAT_WS(',', 'Hello', NULL, 'World') |
+----------------------------------------+
| Hello,World                            |
+----------------------------------------+

Example 5: Concatenating strings with a binary or a blob separator

The following example uses the CONCAT_WS() function to concatenate two strings with a binary value as the separator.

SELECT CONCAT_WS(0x2D, 'Hello', 'World');

The output is:

+-----------------------------------+
| CONCAT_WS(0x2D, 'Hello', 'World') |
+-----------------------------------+
| Hello-World                       |
+-----------------------------------+

The binary value 0x2D is equivalent to the ASCII character ‘-’. Similarly, you can use a blob value as the separator.

There are some other functions that are related to the CONCAT_WS() function in Mariadb. They are:

  • CONCAT(): This function concatenates two or more strings without a separator. It returns NULL if any argument is NULL.
  • CONCAT_WS_X(): This function is similar to the CONCAT_WS() function, except that it does not skip the NULL values in the arguments list. It treats them as empty strings.
  • GROUP_CONCAT(): This function concatenates values from a group of rows with a specified separator. It returns a string or a blob value.

Conclusion

The CONCAT_WS() function is a useful function to join strings with a separator. It can handle NULL values and binary or blob values as the separator. It is similar to the CONCAT() function, but with a separator argument. It is also similar to the CONCAT_WS_X() function, but it skips the NULL values in the arguments list. The GROUP_CONCAT() function is another function that can concatenate values from a group of rows.