MySQL EXPORT_SET() function

Last update on July 19 2023 08:09:41 (UTC/GMT +8 hours)

EXPORT_SET() function

Here is how MySQL EXPORT_SET() works -

1. For every bit of the first argument (which is supplied as an integer but the function works by converting it into bits) it checks whether it is 1 or 0. The order of checking is right to left.

2. If the bit is 1, it returns the second argument.

3. If the bit is 0, it returns the third argument.

4. While displaying, it places a separator (which is the fourth argument) between the return values. And return values are displayed from left to right.

5. The checking continues upto the number of bits specified in the last, i.e. fifth argument. The default value of the fifth argument is 64.

Syntax:

EXPORT_SET(bits, on, off, [separator [, number of bits]])

Arguments:

Name	Description
Bits	(got by converting the supplied integer to bits) which are to be compared if they 1 or 0.
on	If the bit in the first argument is 1, this is returned.
off	If the bit in the second argument is 0, this is returned.
separator	To be placed between the return values while displaying the output.
number of bits	How many bits are to be compared if they are 1 or 0 of the first argument (i.e. bits).

MySQL Version: 8.0

Example : MySQL EXPORT_SET() function

The following MySQL statement returns Y,N,Y. See the explanation -

Code:

SELECT EXPORT_SET(5,'Y','N',',',3);

Explanation:

1. If converted, the first argument 5 stands for 00000101.

2. Now, taking one bit at a time from the right, first bit is 1, so it returns the second argument Y. For the third bit, it returns Y. For all the remaining bits, it returns N.

3. The fourth argument "," as taken as a separator for while displaying the returned bits.

4. While displaying bits returned from left tot right, so the out is Y,N,Y.

Output:

mysql> SELECT EXPORT_SET(5,'Y','N',',',3);
+-----------------------------+
| EXPORT_SET(5,'Y','N',',',3) |
+-----------------------------+
| Y,N,Y                       | 
+-----------------------------+
1 row in set (0.00 sec)

Example:

The following MySQL statement returns 0,0,1,0,0,0,0,0 after performing a EXPORT_SET operation on the given arguments.

Code:

SELECT EXPORT_SET(4,'1','0',',',8);

Output:

mysql> SELECT EXPORT_SET(4,'1','0',',',8);
+-----------------------------+
| EXPORT_SET(4,'1','0',',',8) |
+-----------------------------+
| 0,0,1,0,0,0,0,0             | 
+-----------------------------+
1 row in set (0.00 sec)

Handling NULL Values

Code:

SELECT EXPORT_SET(NULL, 'A', 'B', ',') AS result;

Output:

result|
------+
      |

The above MySQL statement returns NULL after performing a EXPORT_SET operation on the given arguments. When the numeric value is NULL, the result will be NULL.

Video Presentation:

All String Functions (Slides presentation)

Previous: ELT
Next: FIELD