MySQL EXPORT_SET() Function

The MySQL EXPORT_SET() function is a string function used to represent a numerical value using binary digits (usually '1' and '0' or 'Y' and 'N') from right to left.

Syntax

Following is the syntax of MySQL EXPORT_SET() function −

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

Parameters

Following are the parameters accepted by this function −

• bits is the value to be represented.

• on is the string used for every bit set in the value.

• off is the string used for every bit not set in the value.

• separator is the separator to place in between.

• number_of_bits is number of bits required in the result.

Return Value

This function returns a comma-separated string where each bit position of the integer corresponds to inclusion or exclusion of the respective string.

Example

In this example, we are using the EXPORT_SET() function to represent the decimal number '1' using '1' for 'on' and '0' for 'off' with a space separator and six bits −

SELECT EXPORT_SET(1,'1','0',' ',6);

Following is the output of the above code −

Example

Now, we are representing '8' using '1' and '0' with four bits −

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

Output of the above code is as shown below −

Example

In this example, we are using 'Y' and 'N' as 'on' and 'off' strings −

SELECT EXPORT_SET(98, 'Y', 'N', ' ', 8);

The output obtained is as shown below −

Example

You can also pass a column name of a table as an argument to this function and get binary values for all the numbers in it.

Let us create a table named "STUDENTS_TABLE" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE STUDENTS_TABLE (
   name VARCHAR(15),
   marks INT,
   grade CHAR
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO STUDENTS_TABLE VALUES 
('Raju', 80, 'A'),
('Rahman', 60, 'B'),
('Robert', 45, 'C');

The STUDENTS_TABLE obtained is as follows −

Following query retrieves the binary values of the marks in the STUDENTS_TABLE −

SELECT name, marks, EXPORT_SET(marks, '1', '0', ' ', 8) 
FROM STUDENTS_TABLE;

After executing the above code, we get the following output −