MySQL EXPORT_SET() Function

In MySQL, the EXPORT_SET() function generates a string using the specified delimiter based on the bits of the argument.

`EXPORT_SET()` Syntax

Here is the syntax of MySQL EXPORT_SET() function:

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

Parameters

bits: Required. A number. Converting bits to binary and inverting the value of each bit and check each bit.
on: Required. THe string to use if the bit value is 1.
off: Required. THe string to use if the bit value is 0.
separator: Optional. Delimiter or delimited string, the default value is ,.
length: Optional. The number of elements in the set, the default value is 64.

Return value

The EXPORT_SET() function picks the corresponding string according to the bit value, and returns a comma-separated set of strings.

Let’s use EXPORT_SET(5, 'Aa', 'Bb', '#', 4) as an example:

bits = 5, the binary bits of 5 is 101, the total length is 4, then left pad to lenth 4 and it becomes 0101. Then reverse it to 1010.
Use on or off by each bit in 1010:
1. The first bit is 1, so we use Aa.
2. The second bit is 0, so we use Bb.
3. The third bit is 1, so we use Aa.
4. The fourth bit is 0, so we use Bb.
Finally, join all strings from previous step with ‘#’, ang get it: Aa#Bb#Aa#Bb.

`EXPORT_SET()` Examples

SELECT
    EXPORT_SET(5, 'Aa', 'Bb', '#', 4),
    EXPORT_SET(5, 'Y', 'N', ',', 4),
    EXPORT_SET(5, '1', '0', ',', 10),
    EXPORT_SET(0, 'Y', 'N', ',', 4)\G

EXPORT_SET(5, 'Aa', 'Bb', '#', 4): Aa#Bb#Aa#Bb
  EXPORT_SET(5, 'Y', 'N', ',', 4): Y,N,Y,N
 EXPORT_SET(5, '1', '0', ',', 10): 1,0,1,0,0,0,0,0,0,0
  EXPORT_SET(0, 'Y', 'N', ',', 4): N,N,N,N

On this page