How the MAKE_SET() function works in Mariadb?

The MAKE_SET() function is a string function that returns a set of strings that are selected from a list of strings based on a given bit mask. The MAKE_SET() function is useful for creating a comma-separated list of values from a set of options.

Syntax

The syntax of the MAKE_SET() function is as follows:

MAKE_SET(bits, string1, string2, ..., stringN)

The bits argument is an integer value that represents a bit mask. The string1, string2, …, stringN arguments are the strings to be selected from. The number of strings can be up to 64, corresponding to the number of bits in the bit mask.

The MAKE_SET() function returns a string value that is a comma-separated list of the strings that are selected from the string1, string2, …, stringN arguments based on the bits argument. If the bits argument is 0, the function returns an empty string. If the bits argument is NULL, the function returns NULL. If any of the string1, string2, …, stringN arguments is NULL, it is ignored.

Examples

Example 1: Basic usage of the MAKE_SET() function

The following example shows how to use the MAKE_SET() function with a simple bit mask and a list of strings. It returns a set of strings that are selected from the list of strings based on the bit mask.

SELECT MAKE_SET(9, 'a', 'b', 'c', 'd');

The output is:

a,d

This means that the MAKE_SET() function selects the strings ‘a’ and ’d’ from the list of strings based on the bit mask 9. The bit mask 9 is equivalent to the binary value 1001, which means that the first and the fourth bits are set to 1, while the rest are set to 0. Therefore, the first and the fourth strings in the list are selected, while the rest are ignored.

Example 2: Using the MAKE_SET() function with a bit mask that has more bits than the number of strings

The following example shows what happens when the MAKE_SET() function is used with a bit mask that has more bits than the number of strings. It returns a set of strings that are selected from the list of strings based on the bit mask, ignoring the extra bits.

SELECT MAKE_SET(15, 'a', 'b', 'c');

The output is:

a,b,c

This means that the MAKE_SET() function selects all the strings from the list of strings based on the bit mask 15. The bit mask 15 is equivalent to the binary value 1111, which means that all the bits are set to 1. However, since there are only three strings in the list, the fourth bit is ignored, and all the strings are selected.

Example 3: Using the MAKE_SET() function with a NULL argument

The following example shows what happens when the MAKE_SET() function is used with a NULL argument. It returns NULL, indicating that the result is undefined.

SELECT MAKE_SET(NULL, 'a', 'b', 'c');

The output is:

NULL

This means that the MAKE_SET() function returns NULL when the bits argument is NULL.

Related Functions

There are some other functions that are related to the MAKE_SET() function in Mariadb. They are:

• FIND_IN_SET(): This function returns the position of a string within a comma-separated list of strings. It is the opposite of the MAKE_SET() function, meaning that FIND_IN_SET(MAKE_SET(x, y), y) = x for any integer x and string y.
• ELT(): This function returns the string at a specified position in a list of strings. It is similar to the MAKE_SET() function, except that it returns a single string instead of a set of strings.
• FIELD(): This function returns the position of a string in a list of strings. It is similar to the FIND_IN_SET() function, except that it does not require the list of strings to be comma-separated.
• BIT_COUNT(): This function returns the number of bits that are set to 1 in a binary representation of a number. It is related to the MAKE_SET() function, as it can be used to calculate the number of strings that are selected from a list based on a bit mask.

Here are some examples of using these related functions:

-- Get the position of 'c' in the comma-separated list of strings 'a,b,c,d'
SELECT FIND_IN_SET('c', 'a,b,c,d');

-- Get the string at the 3rd position in the list of strings 'a', 'b', 'c', 'd'
SELECT ELT(3, 'a', 'b', 'c', 'd');

-- Get the position of 'c' in the list of strings 'a', 'b', 'c', 'd'
SELECT FIELD('c', 'a', 'b', 'c', 'd');

-- Get the number of bits that are set to 1 in the binary representation of 9
SELECT BIT_COUNT(9);

Conclusion

In this article, we have learned how the MAKE_SET() function works in Mariadb. We have seen its syntax, examples, and related functions. We have also learned how to use the MAKE_SET() function to return a set of strings that are selected from a list of strings based on a given bit mask. The MAKE_SET() function is a useful function to create a comma-separated list of values from a set of options.