MySQL EXPORT_SET() function

Last update on August 19 2022 21:51:22 (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: 5.6

Video Presentation:

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.

Sample 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);

Sample 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)

All String Functions

Previous: ELT
Next: FIELD