MariaDB CONCAT_WS() Function

In MariaDB, CONCAT_WS() is a built-in string function that concatenates other string arguments using the specified delimiter and returns the result.

If you don’t need to concatenate strings with delimiters, use the CONCAT() function.

MariaDB CONCAT_WS() Syntax

Here is the syntax of the MariaDB CONCAT_WS() function:

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

Parameter Description

separator

Required. delimiter. You can use single characters, or strings.

str1, str2, ..., strN

Required. A list of strings to concatenate.

NULL Values ​​in the string list will be ignored.

If you do not specify parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT_WS'.

Return value

The MariaDB CONCAT_WS() function concatenats the given multiple strings using a delimiter and returns the result.

The CONCAT_WS() function will return NULL if the delimiter separator is NULL.

MariaDB CONCAT_WS() Examples

Single character delimiter

The following statement uses the MariaDB CONCAT_WS() function to concatenate the fruit names Apple, Peach, and Banana with the delimiter ,:

SELECT CONCAT_WS( ',', 'Apple', 'Peach', 'Banana');

Output:

+---------------------------------------------+
| CONCAT_WS( ',', 'Apple', 'Peach', 'Banana') |
+---------------------------------------------+
| Apple,Peach,Banana                          |
+---------------------------------------------+

String delimiter

You can use a string as delimiter.

The following statement uses the MariaDB CONCAT_WS() function to concatenate the fruit names Apple, Peach, with the delimiter ----:

SELECT CONCAT_WS( '-----', 'Apple', 'Peach', 'Banana');

Output:

+-------------------------------------------------+
| CONCAT_WS( '-----', 'Apple', 'Peach', 'Banana') |
+-------------------------------------------------+
| Apple-----Peach-----Banana                      |
+-------------------------------------------------+

NULL parameters

NULL values ​​in the string list are ignored by CONCAT_WS().

The following example illustrates this:

SELECT CONCAT_WS( ',', 'Apple', NULL, 'Banana');

Output:

+------------------------------------------+
| CONCAT_WS( ',', 'Apple', NULL, 'Banana') |
+------------------------------------------+
| Apple,Banana                             |
+------------------------------------------+

binary string

If you use a binary string, the CONCAT_WS() function also returns a binary string.

SELECT CONCAT_WS( ',', BINARY 'Apple', 'Peach');

Output:

+------------------------------------------+
| CONCAT_WS( ',', BINARY 'Apple', 'Peach') |
+------------------------------------------+
| Apple,Peach                              |
+------------------------------------------+

You can use the COLLATION() function to check the collation of the results:

SELECT COLLATION(CONCAT_WS( ',', BINARY 'Apple', 'Peach'));

Output:

+-----------------------------------------------------+
| COLLATION(CONCAT_WS( ',', BINARY 'Apple', 'Peach')) |
+-----------------------------------------------------+
| binary                                              |
+-----------------------------------------------------+

Conclusion

The MariaDB CONCAT_WS() function concatenates the specified list of strings using the specified delimiter and returns the result.