MySQL MAKE_SET() Function

---

---

The MySQL MAKE_SET() function is used to retrieve string values (separated by commas) from a set of values based on the given bit(s).

This function accepts bit values separated by "|" and series of strings as parameters and returns a string where each bit value is replaced with the corresponding value from the series of strings.

Syntax

Following is the syntax of MySQL MAKE_SET() function −

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

Parameters

This function takes a numeric value and a list of strings as parameter.

Return Value

This function returns a comma-separated string where each bit position of the integer corresponds to inclusion or exclusion of the respective string.

Example

In the following example, we are using the MAKE_SET() function with a bit value of 3. The binary representation of 3 is '11', i.e. having both the positions as 1. Hence, it returns 'apple' and 'banana' from the series of strings 'apple', 'banana', 'mango', and 'grapes' since their position corresponds to the binary bits −

SELECT MAKE_SET(3,'apple','banana','mango', 'grapes');

Following is the output of the above code −

MAKE_SET(3,'apple','banana','mango', 'grapes')
apple,banana

Example

Here, the first bit is 1 i.e. 001, the rightmost digit is 1 hence it returns 'apple' (first position), second bit is 4 i.e. 100, the third position is 1 (from right to left), hence it returns 'mango' −

SELECT MAKE_SET(1|4,'apple','banana','mango', 'grapes');

Output of the above code is as shown below −

MAKE_SET(1|4,'apple','banana','mango', 'grapes')
apple,mango

Example

Even if multiple bit values are used repeatedly, the resulting string includes those values only once (no duplicates) −

SELECT MAKE_SET(3|3|3|3,'apple','banana','mango', 'grapes');

The output obtained is as follows −

MAKE_SET(3|3|3|3,'apple','banana','mango', 'grapes')
apple,banana

Example

If you pass NULL values as bits in the series of strings, they are not included in the result (no NULL values) −

SELECT MAKE_SET(3|2,'Java', NULL, 'JavaFX','OpenCV', NULL, 'CoffeeScript', 'WebGL');

The result produced is as follows −

MAKE_SET(3|2,'Java', NULL, 'JavaFX','OpenCV', NULL, 'CoffeeScript', 'WebGL')
Java

Example

If any of the arguments passed to this function is NULL, it returns NULL −

SELECT LOCATE(NULL, 'Tutorialspoint');

Following is the output of the above code −

LOCATE(NULL, 'Tutorialspoint')
NULL

Example

You can also pass a series of numbers instead of strings as arguments to this function −

SELECT MAKE_SET(5, 558, 5558, 66988, 6547, 669, 368);

After executing the above code, we get the following output −

MAKE_SET(5, 558, 5558, 66988, 6547, 669, 368)
558,66988