MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

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

bits Required. Specify the bits set.
str1, str2, str3,... Required. Specify the list of strings.

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.

MAKE_SET() Function

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:

EmpIDNameDepartment1Department2Department3
1JohnHRAdminTreasury
2MarryITFinance
3JoFinanceMarketing
4KimMarketingSales
5RameshITFinanceMarketing
6HuangITMarketingSales

The query 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:

EmpIDNameDepartment1Department2Department3MAKE_SET_Value
1JohnHRAdminTreasuryHR,Admin,Treasury
2MarryITFinanceIT,Finance
3JoFinanceMarketingFinance,Marketing
4KimMarketingSalesMarketing,Sales
5RameshITFinanceMarketingIT,Finance,Marketing
6HuangITMarketingSalesIT,Marketing,Sales

❮ MySQL Functions

5