MySQL MAKE_SET() Function

The MySQL MAKE_SET() function returns a set value (a string containing substrings separated by "," characters) consisting of the strings that have the corresponding bit in bits set. NULL values in str1, str2, ... are not appended to the result.

Syntax

MAKE_SET(bits, str1, str2, str3,...)

Parameters

Return Value

Returns a set value (a string containing substrings separated by "," characters) consisting of the strings that have the corresponding bit in bits set.

Example 1:

The example below shows the usage of MAKE_SET() function.

mysql> SELECT MAKE_SET(1, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning'

mysql> SELECT MAKE_SET(1|2, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,MySQL'

mysql> SELECT MAKE_SET(1|4, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,is'

mysql> SELECT MAKE_SET(1|6, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,MySQL,is'

mysql> SELECT MAKE_SET(1|8, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,fun'

mysql> SELECT MAKE_SET(1|14, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,MySQL,is,fun'

mysql> SELECT MAKE_SET(15, 'Learning', 'MySQL', 'is', 'fun');
Result: 'Learning,MySQL,is,fun'

mysql> SELECT MAKE_SET(15, 'A', 'B', NULL, 'D');
Result: 'A,B,D'

Example 2:

Consider a database table called Employee with the following records:

The statement given below can be used to get the set value specified by bit in bits set.

/* Binary of 28 is 11100 */
SELECT *, 
MAKE_SET(28, EmpID, Name, Department1, Department2, Department3) AS MAKE_SET_Value 
FROM Employee;

This will produce the result as shown below: