MariaDB EXPORT_SET() Function

In MariaDB, EXPORT_SET() is a built-in string function that generates a string using the specified delimiter based on the bits of the argument.

MariaDB `EXPORT_SET()` Syntax

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

EXPORT_SET(bits, on, off, separator, length)

Parameters

bits: Required. A number. Converting bits to binary and inverting (from right to left) the value of each bit determines whether on or off appears in that position.
on: Required. String to use when the bit value is 1.
off: Required. String to use when the bit value is 0.
separator: Optional. Separator string, default is ,.
length: Optional. The number of elements in the collection, the default value is 64.

You should provide at least 3 parameters, otherwise MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'EXPORT_SET'.

Return value

The MariaDB EXPORT_SET() function selects the corresponding string according to the reversed value of binary of bits, and returns a comma-separated string set.

The EXPORT_SET() function will return NULL if any argument is NULL.

For example EXPORT_SET(5, 'Aa', 'Bb', '#', 4):

bits = 5, the binary of 5 is 101(this result can be returned by the BIN() function), the total length is 4, and the left complement 0 becomes 0101. Then reverse the value (right to left) to 1010.
Use the value of on or off according each bit in 1010:
1. The first bit is 1, use Aa.
2. The second bit is 0, use Bb.
3. The third bit is 1, use Aa.
4. The fourth bit is 0, use Bb.
Finally, combine them with # and become to Aa#Bb#Aa#Bb.

MariaDB `EXPORT_SET()` Examples

Basic example

This statement shows the basic usage of the MariaDB EXPORT_SET():

SELECT EXPORT_SET(5, 'Aa', 'Bb', '#', 4);

Output:

+-----------------------------------+
| EXPORT_SET(5, 'Aa', 'Bb', '#', 4) |
+-----------------------------------+
| Aa#Bb#Aa#Bb                       |
+-----------------------------------+

Defaults

If you do not provide a delimiter, MariaDB EXPORT_SET() will use a comma , as the default delimiter.

If you don’t specify a number, MariaDB EXPORT_SET() will use 64 as the default number of bits.

SELECT EXPORT_SET(5, 'A', 'B');

Output:

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(5, 'A', 'B')                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------+
| A,B,A,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B,B |
+---------------------------------------------------------------------------------------------------------------------------------+

Conclusion

In MariaDB, EXPORT_SET() is a built-in string function that returns a string concatenated by the specified delimiter from the string indicated by the binary bits (from high to low) of the first argument.

MariaDB EXPORT_SET() Syntax