MySQL MAKE_SET() Function

In MySQL, the MAKE_SET() function returns a set of comma-separated strings that have the corresponding bit in bits set.

MAKE_SET() Syntax

Here is the syntax of MySQL MAKE_SET() function:

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

Parameters

bits
Required. It is a number or an expression that have a decimal or binary value.
str1, str2, ...
Required. You should provide at least one parameter.

Return value

The MAKE_SET() function picks all strings that have the corresponding bit in bits set, and returns a comma-separated set of picked strings.

NULL value in str1, str2, ... will not appear in the result.

Let’s take a look at the process of MAKE_SET(5, 'a', 'b', 'c', 'd'):

  1. The bits is 6.

  2. The binary representation of 6 is 110. You can get the binary value of a given number by using BIN() function.

  3. Reverse 110 and it is 011.

  4. Pick the corresponding string according to every bit in 011:

    1. The first bit is 0, so 'a' will be skipped.
    2. The second bit is 1, so 'b' will be picked.
    3. The third bit is 1, so 'c' will be picked.

  5. Join the picked strings 'b' and 'c' with comma, that is 'b,c'.

  6. MAKE_SET(5, 'a', 'b', 'c', 'd') returns 'b,c'.

   bits: 6
 binary: 110
reverse: 011

         0 1 1
         | | | |
         x v v x
         | | | |
         a b c d

 return: b,c

MAKE_SET() Examples

SELECT
    MAKE_SET(0, 'a', 'b', 'c', 'd') as `0`,
    MAKE_SET(1, 'a', 'b', 'c', 'd') as `1`,
    MAKE_SET(2, 'a', 'b', 'c', 'd') as `2`,
    MAKE_SET(3, 'a', 'b', 'c', 'd') as `3`,
    MAKE_SET(4, 'a', 'b', 'c', 'd') as `4`,
    MAKE_SET(5, 'a', 'b', 'c', 'd') as `5`,
    MAKE_SET(6, 'a', 'b', 'c', 'd') as `6`,
    MAKE_SET(7, 'a', 'b', 'c', 'd') as `7`,
    MAKE_SET(8, 'a', 'b', 'c', 'd') as `8`,
    MAKE_SET(9, 'a', 'b', 'c', 'd') as `9`;

+---+---+---+-----+---+-----+-----+-------+---+-----+
| 0 | 1 | 2 | 3   | 4 | 5   | 6   | 7     | 8 | 9   |
+---+---+---+-----+---+-----+-----+-------+---+-----+
|   | a | b | a,b | c | a,c | b,c | a,b,c | d | a,d |
+---+---+---+-----+---+-----+-----+-------+---+-----+