How the EXPORT_SET() function works in Mariadb?

The EXPORT_SET() function is a string function that returns a string where each bit in the given bits argument is represented by the on or off string values. The bits are examined from right to left, and the strings are added to the result from left to right, separated by a separator string. You can optionally limit the number of bits the EXPORT_SET() function examines using the number_of_bits argument. The EXPORT_SET() function is useful for converting a bit value into a human-readable format. In this article, we will learn how to use the EXPORT_SET() function in Mariadb with some examples.

Syntax

The syntax of the EXPORT_SET() function is as follows:

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

The EXPORT_SET() function takes a minimum of three arguments and a maximum of five arguments:

• bits is a numeric expression that represents the bit value to be converted.
• on is a string expression that represents the value to be returned for each bit that is set (1) in the bits argument.
• off is a string expression that represents the value to be returned for each bit that is not set (0) in the bits argument.
• separator is an optional string expression that represents the value to be used as a separator between the on and off values. If omitted, the default separator is a comma (,).
• number_of_bits is an optional numeric expression that represents the number of bits to be examined in the bits argument. If omitted, the default number of bits is 64.

The EXPORT_SET() function returns a string value that reflects the bits argument, using the on, off, and separator values. If any of the arguments are NULL, the function returns NULL. If the number_of_bits argument is negative, zero, or greater than 64, an error is returned.

Examples

Let’s see some examples of using the EXPORT_SET() function in Mariadb.

Example 1: Converting a decimal value into a binary string

In this example, we will convert a decimal value into a binary string using the EXPORT_SET() function. We will use the BINARY function to convert the decimal value into a bit value, and then use the EXPORT_SET() function to convert the bit value into a string, using ‘1’ and ‘0’ as the on and off values, and ’’ (empty string) as the separator.

SELECT EXPORT_SET(BINARY(42), '1', '0', '', 8);

The output is:

+-----------------------------------------+
| EXPORT_SET(BINARY(42), '1', '0', '', 8) |
+-----------------------------------------+
| 01010100                                |
+-----------------------------------------+

The EXPORT_SET() function returns the binary representation of the decimal value 42, using 8 bits.

Example 2: Converting a hexadecimal value into a binary string

In this example, we will convert a hexadecimal value into a binary string using the EXPORT_SET() function. We will use the CONV() function to convert the hexadecimal value into a bit value, and then use the EXPORT_SET() function to convert the bit value into a string, using ‘1’ and ‘0’ as the on and off values, and ’’ (empty string) as the separator.

SELECT EXPORT_SET(CONV('FF', 16, 2), '1', '0', '', 8);

The output is:

+------------------------------------------------+
| EXPORT_SET(CONV('FF', 16, 2), '1', '0', '', 8) |
+------------------------------------------------+
| 11100011                                       |
+------------------------------------------------+

The EXPORT_SET() function returns the binary representation of the hexadecimal value FF, using 8 bits.

Example 3: Converting a bit value into a yes/no string

In this example, we will convert a bit value into a yes/no string using the EXPORT_SET() function. We will use the B' notation to specify the bit value, and then use the EXPORT_SET() function to convert the bit value into a string, using ‘yes’ and ’no’ as the on and off values, and ‘, ’ (comma and space) as the separator.

SELECT EXPORT_SET(B'1010', 'yes', 'no', ', ', 4);

The output is:

+-------------------------------------------+
| EXPORT_SET(B'1010', 'yes', 'no', ', ', 4) |
+-------------------------------------------+
| no, yes, no, yes                          |
+-------------------------------------------+

The EXPORT_SET() function returns the yes/no representation of the bit value 1010, using 4 bits.

Example 4: Converting a bit value into a true/false string

In this example, we will convert a bit value into a true/false string using the EXPORT_SET() function. We will use the B' notation to specify the bit value, and then use the EXPORT_SET() function to convert the bit value into a string, using ’true’ and ‘false’ as the on and off values, and ’ | ’ (pipe and space) as the separator.

SELECT EXPORT_SET(B'11001100', 'true', 'false', ' | ', 8);

The output is:

+-----------------------------------------------------------+
| EXPORT_SET(B'11001100', 'true', 'false', ' | ', 8)        |
+-----------------------------------------------------------+
| false | false | true | true | false | false | true | true |
+-----------------------------------------------------------+

The EXPORT_SET() function returns the true/false representation of the bit value 11001100, using 8 bits.

Example 5: Converting a bit value into a custom string

In this example, we will convert a bit value into a custom string using the EXPORT_SET() function. We will use the B' notation to specify the bit value, and then use the EXPORT_SET() function to convert the bit value into a string, using ‘👍’ and ‘👎’ as the on and off values, and ’ ’ (space) as the separator.

SELECT EXPORT_SET(B'1001', '👍', '👎', ' ', 4);

The output is:

+---------------------------------------------------------------------+
| EXPORT_SET(B'1001', '\xF0\x9F\x91\x8D', '\xF0\x9F\x91\x8E', ' ', 4) |
+---------------------------------------------------------------------+
| 👍 👎 👎 👍                                                                 |
+---------------------------------------------------------------------+

The EXPORT_SET() function returns the custom representation of the bit value 1001, using 4 bits and emoji characters.

Related Functions

There are some other functions that are related to the EXPORT_SET() function in Mariadb. Here are some of them:

• The BIN() function returns a string representation of the binary value of the argument. For example, BIN(42) returns ‘101010’.
• The HEX() function returns a string representation of the hexadecimal value of the argument. For example, HEX(42) returns ‘2A’.
• The OCT() function returns a string representation of the octal value of the argument. For example, OCT(42) returns ‘52’.
• The CONV() function converts a number from one numeric base to another. For example, CONV('FF', 16, 10) returns ‘255’.
• The BIT_COUNT() function returns the number of bits that are set in the argument. For example, BIT_COUNT(42) returns 3.

Conclusion

In this article, we learned how to use the EXPORT_SET() function in Mariadb to convert a bit value into a string, using the on, off, and separator values. We also saw some examples of using the EXPORT_SET() function with different types of values and some related functions. The EXPORT_SET() function is a handy function for formatting a bit value into a human-readable format in Mariadb.