Google Git
Sign in
apache/doris-website/refs/heads/master/./docs/sql-manual/sql-functions/scalar-functions/string-functions/export-set.md
blob: d3b016cdb574379af102f76218c6c698c8cca673 [file] [view]
---
{
"title": "EXPORT-SET",
"language": "en",
"description": "EXPORTSET 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,"
}
---
## 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](https://dev.mysql.com/doc/refman/8.4/en/string-functions.html#function_export-set).
## Syntax
```sql
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
```sql
SELECT EXPORT_SET(-2, '1', '0');
```
```text
+---------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------------------------------------------------------------------------------------------------------------------------+
```
```sql
SELECT EXPORT_SET(5, '1', '0', '||', 5);
```
```text
+----------------------------------+
| EXPORT_SET(5, '1', '0', '||', 5) |
+----------------------------------+
| 1||0||1||0||0 |
+----------------------------------+
```
```sql
SELECT `bits`, `on`, `off`, `sep`, `num_of_b`
FROM `test_export_set`;
```
```text
+-------------+--------+-------+-------+----------+
| 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 |
+-------------+--------+-------+-------+----------+
```
```sql
SELECT EXPORT_SET(`bits`, `on`, `off`, `sep`, `num_of_b`)
FROM `test_export_set`;
```
```text
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------+--------+-------+-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
```sql
-- max value: 2^64 - 1
SELECT EXPORT_SET(18446744073709551615, '1', '0');
```
```text
+---------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------------------------------------------------------------------------------------------------------------------------+
```
```sql
-- 2^64 out of bound, the bits will be set to 2^63 - 1
SELECT EXPORT_SET(18446744073709551616, '1', '0');
```
```text
+---------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------------------------------------------------------------------------------------------------------------------------+
```
```sql
-- min value: -2^63
SELECT EXPORT_SET(-9223372036854775808, '1', '0');
```
```text
+---------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------------------------------------------------------------------------------------------------------------------------+
```
```sql
-- out of bound, the bits will be set to -2^63
SELECT EXPORT_SET(-184467440737095516161, '1', '0');
```
```text
+---------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------------------------------------------------------------------------------------------------------------------------+
```