MariaDB MAKE_SET() Function

In MariaDB, MAKE_SET() is a string function that returns a comma-separated string collection, it determines whether other string parameters are added to the result set by the binary value of the first parameter.

MariaDB MAKE_SET() Syntax

Here is the syntax of the MariaDB MAKE_SET() function:

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

Parameters

bits

Required. A number. Converting bits to binary and inverting to determine whether str1, str2, ... is present in the result.

str1, str2, ...

Required. Alternative strings.

Return value

The MariaDB MAKE_SET() function selects the corresponding string from str1, str2, ... corresponding bits. If the binary bit is 1, add the corresponding parameter will be added to the result collection.

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

For example bits = 6, the binary of 6 is 110, invert 110 to 011, so MAKE_SET() returns str2,str3.

If bits is 0, the MAKE_SET() function will return an empty string.

MariaDB MAKE_SET() Examples

SELECT
    MAKE_SET(0, 'a', 'b', 'c', 'd'),
    MAKE_SET(1, 'a', 'b', 'c', 'd'),
    MAKE_SET(2, 'a', 'b', 'c', 'd'),
    MAKE_SET(3, 'a', 'b', 'c', 'd'),
    MAKE_SET(4, 'a', 'b', 'c', 'd')\G

Output:

*************************** 1\. row ***************************
MAKE_SET(0, 'a', 'b', 'c', 'd'):
MAKE_SET(1, 'a', 'b', 'c', 'd'): a
MAKE_SET(2, 'a', 'b', 'c', 'd'): b
MAKE_SET(3, 'a', 'b', 'c', 'd'): a,b
MAKE_SET(4, 'a', 'b', 'c', 'd'): c

In this example,

  • bits = 1, the binary of 1 is 1, and invert to 1, then 'a' corresponding 1, so return 'a'.
  • bits = 2, the binary of 2 is 10, and invert to 01, then 'a' corresponds to 0, and 'b' corresponds to 1, so return 'b'.
  • bits = 3, the binary of 3 is 11, and invert to 11, then 'a' corresponds to 1, and 'b' corresponds to 1, so return 'a,b'.
  • bits = 4, the binary of 4 is 100, and invert to 001, then 'a' and 'b' correspond to 0, and 'c' corresponds to 1, so return 'c'.

You can use the BIN() function calculate the binary of a number.

Conclusion

The MariaDB MAKE_SET() function returns a comma-separated string set, the function determines whether other string parameters are added to the result set by the binary value of the first parameter.