In MariaDB, EXPORT_SET() is a built-in string function that returns a string that reflects the bits in the specified value. For every bit set in the specified value, you get an “on” string and for every bit not set in the value, you get an “off” string.
It accepts a minimum of three arguments, plus two optional arguments.
Syntax
The syntax goes like this:
EXPORT_SET(bits, on, off[, separator[, number_of_bits]])The following table provides an explanation of those arguments.
bits | The value for which you’d like the results returned. Provided as an integer, but it’s converted to bits. For every bit that’s set in this value you get an on string, and for every bit that’s not set in the value, you get an off string. The bits are examined from right to left (from low-order to high-order bits). |
on | The string that’s returned for any on bits. |
off | The string that’s returned for any off bits. |
separator | Optional argument that you can use to specify the separator to use. The default value is the comma character. Therefore, if you don’t specify this argument a comma is used as the separator. |
number_of_bits | The number of bits to examine. The default value is 64. If you provide a larger value, this is silently clipped to 64 if larger than 64. |
Example
Here’s a basic example:
SELECT EXPORT_SET(13,'On','Off',',',4);Result:
+---------------------------------+ | EXPORT_SET(13,'On','Off',',',4) | +---------------------------------+ | On,Off,On,On | +---------------------------------+
We can see that the first, third, and fourth bits are set, but the second is not.
We can use the BIN() function to return a similar result, but in reverse order:
SELECT BIN(13);Result:
+---------+ | BIN(13) | +---------+ | 1101 | +---------+
The BIN() function returns a string representation of the binary value of the given longlong. In this example, it returned three 1s.
In our EXPORT_SET() example, we specified that on and off should be used to represent the 1 and 0 respectively. We can change that though (below).
Also, with EXPORT_SET(), strings are added to the result from left to right. Therefore the result from EXPORT_SET() looks like a mirror image of the result from BIN().
Change the On/Off Values
Here it is again, but this time we use a different string for the on and off states.
SELECT EXPORT_SET(7,'1','0',',',4);Result:
+-----------------------------+ | EXPORT_SET(7,'1','0',',',4) | +-----------------------------+ | 1,1,1,0 | +-----------------------------+
This time we’re using 1 and 0, similar to the what the BIN() function returns, but with an added separator (and reversed).
Change the Separator
The fourth (optional) argument specifies what separator to use. Here it is with a different separator:
SELECT EXPORT_SET(7,'True','False','-',4);Result:
+------------------------------------+ | EXPORT_SET(7,'True','False','-',4) | +------------------------------------+ | True-True-True-False | +------------------------------------+
Change the Number of Bits to Examine
The fifth (optional) argument specifies how many bits to examine. In the previous examples, we used 4 as the number of bits to examine. We can increase this if we want:
SELECT EXPORT_SET(7,'1','0',',',10);Result:
+------------------------------+ | EXPORT_SET(7,'1','0',',',10) | +------------------------------+ | 1,1,1,0,0,0,0,0,0,0 | +------------------------------+
In this case, all of the extra bits are not set. Let’s increase the value of the first argument to see how this affects the result:
SELECT EXPORT_SET(172,'1','0',',',10);Result:
+--------------------------------+ | EXPORT_SET(172,'1','0',',',10) | +--------------------------------+ | 0,0,1,1,0,1,0,1,0,0 | +--------------------------------+
Default Values
As mentioned, the fourth and fifth arguments are optional. When you omit these, the default values are used.
Default Number of Bits
If we remove the last argument, 64 bits are examined:
SELECT EXPORT_SET(172,'1','0','');Result:
+------------------------------------------------------------------+ | EXPORT_SET(172,'1','0','') | +------------------------------------------------------------------+ | 0011010100000000000000000000000000000000000000000000000000000000 | +------------------------------------------------------------------+
In this case I also removed the separator in order to make the output more concise.
Default Separator
We can also remove the separator argument. When we do this, EXPORT_SET() uses the comma as a default separator.
SELECT EXPORT_SET(123456789,'1','0');Result:
+-----------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+ | EXPORT_SET(123456789,'1','0') | +---------------------------------------------------------------------------------------------------------------------------------+ | 1,0,1,0,1,0,0,0,1,0,1,1,0,0,1,1,1,1,0,1,1,0,1,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 | +---------------------------------------------------------------------------------------------------------------------------------+
Null Arguments
If any of the arguments are null, the result is null:
SELECT
EXPORT_SET(null,'1','0') AS "1",
EXPORT_SET(7,null,'0') AS "2",
EXPORT_SET(7,'1',null) AS "3",
EXPORT_SET(7,'1','0',null,4) AS "4",
EXPORT_SET(7,'1','0',',',null) AS "5";Result:
+------+------+------+------+------+ | 1 | 2 | 3 | 4 | 5 | +------+------+------+------+------+ | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+
Missing Arguments
Calling EXPORT_SET() without passing any arguments (or with the wrong number of arguments) results in an error:
SELECT EXPORT_SET();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'EXPORT_SET'