MySQL EXPORT_SET() function

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:

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