EXPORT-SET

Description

EXPORT_SET is used to convert each bit of an integer into a specified string and concatenate them into a result string. For each bit in bits that is 1, the corresponding position in the result will display the on string; for each bit that is 0, the off string will be displayed. The bits are checked in order from right to left (i.e., from the least significant bit to the most significant bit), but concatenated into the result string from left to right. The bits are separated by the separator (default is a comma ,).

The number of bits displayed is determined by the number_of_bits parameter, with a default of 64 bits. If not specified or out of range (greater than 64), it is automatically treated as 64 bits.

This function behaves similarly to MySQL's EXPORT_SET function.

Syntax

EXPORT_SET(bits, on, off[, separator[, number_of_bits]])

Parameters

Parameter	Description
bits	The integer type(LARGEINT) used for conversion. If the value is less than -2^63, treat it as -2^63. If it is greater than 2^64 - 1, treat it as 2^63 - 1.
on	The string displayed when the bit is 1
off	The string displayed when the bit is 0
separator	Optional, the string used to separate the bits, default is ','
number_of_bits	Optional, the number of bits to display in the result, supports integer type(INT). The default value is 64, and it will be automatically handled with the default value if it exceeds the range [0, 64].

Return value

Returns a string. For each bit in bits, from the least significant bit to the most significant bit, if it is 1, it is replaced with on; if it is 0, it is replaced with off, and they are concatenated in order. The bits are separated by separator, and the final result displays number_of_bits bits.

If number_of_bits is out of range [-2^31, 2^31 - 1] or any parameter in the function is NULL, return NULL.

Example

SELECT EXPORT_SET(-2, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(-2, '1', '0')                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------+
| 0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 |
+---------------------------------------------------------------------------------------------------------------------------------+

SELECT EXPORT_SET(5, '1', '0', '||', 5);

+----------------------------------+
| EXPORT_SET(5, '1', '0', '||', 5) |
+----------------------------------+
| 1||0||1||0||0                    |
+----------------------------------+

SELECT `bits`, `on`, `off`, `sep`, `num_of_b`
FROM `test_export_set`;

+-------------+--------+-------+-------+----------+
| bits        | on     | off   | sep   | num_of_b |
+-------------+--------+-------+-------+----------+
|          -1 | 1      | 0     | ,     |       50 |
|          -2 | 1      | 0     |       |       64 |
|           5 | Y      | N     | ,     |        5 |
|           5 | 1      | 0     |       |       64 |
|           5 |        | 0     |       |       65 |
|           6 | 1      |       |       |       63 |
| 19284249819 | 1      | 0     | ,     |       64 |
|           9 | apache | doris | |123| |       64 |
|        NULL | 1      | 0     | ,     |        5 |
|           5 | NULL   | 0     |       |        5 |
|           5 | 1      | NULL  | ,     |       10 |
|           5 | 1      | 0     | NULL  |       10 |
|           5 | 1      | 0     | ,     |     NULL |
+-------------+--------+-------+-------+----------+

SELECT EXPORT_SET(`bits`, `on`, `off`, `sep`, `num_of_b`)
FROM `test_export_set`;

+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bits        | on     | off   | sep   | num_of_b | ans                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|          -1 | 1      | 0     | ,     |       50 | 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|          -2 | 1      | 0     |       |       64 | 0111111111111111111111111111111111111111111111111111111111111111                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|           5 | Y      | N     | ,     |        5 | Y,N,Y,N,N                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|           5 | 1      | 0     |       |       64 | 1010000000000000000000000000000000000000000000000000000000000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|           5 |        | 0     |       |       65 | 00000000000000000000000000000000000000000000000000000000000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|           6 | 1      |       |       |       63 | 11                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 19284249819 | 1      | 0     | ,     |       64 | 1,1,0,1,1,0,1,1,0,0,1,1,0,0,1,0,0,1,1,1,0,1,1,0,1,0,1,1,1,1,1,0,0,0,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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|           9 | apache | doris | |123| |       64 | apache|123|doris|123|doris|123|apache|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris|123|doris |
|        NULL | 1      | 0     | ,     |        5 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | NULL   | 0     |       |        5 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | NULL  | ,     |       10 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | 0     | NULL  |       10 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|           5 | 1      | 0     | ,     |     NULL | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- max value: 2^64 - 1
SELECT EXPORT_SET(18446744073709551615, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(18446744073709551615, '1', '0')                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------+
| 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 |
+---------------------------------------------------------------------------------------------------------------------------------+

-- 2^64 out of bound, the bits will be set to 2^63 - 1
SELECT EXPORT_SET(18446744073709551616, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(18446744073709551616, '1', '0')                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------+
| 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0 |
+---------------------------------------------------------------------------------------------------------------------------------+

-- min value: -2^63
SELECT EXPORT_SET(-9223372036854775808, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(-9223372036854775808, '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,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,1 |
+---------------------------------------------------------------------------------------------------------------------------------+

-- out of bound, the bits will be set to -2^63
SELECT EXPORT_SET(-184467440737095516161, '1', '0');

+---------------------------------------------------------------------------------------------------------------------------------+
| EXPORT_SET(-184467440737095516161, '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,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,1 |
+---------------------------------------------------------------------------------------------------------------------------------+