MySQL8 MAKE_SET() Functions – String Functions

MAKE_SET() Functions returns a comma-separated list of values that match with bits of a base 10 number converted to its binary equivalent.

When we call the function, we specify any number of strings (separated by a comma), as well as one or more bit values that determine which strings to return in the set value.

string1 corresponds to bit 0, string2 to bit 1, and so on.

NULL values in str1, str2, .. are not appended to the result. See example 4.

MySQL MAKE_SET() Functions: Syntax

MySQL MAKE_SET() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
string, Required, String , It represents any number of strings separated by a comma.
bits, Required, String , It represents one or more bit values that determine which strings to return in the set value.
[/table]

MySQL MAKE_SET() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 5.7
[/table]

MAKE_SET() Example 1 : Basic Usage

Below is the example to show it. Here we have comma-separated list of values and  bit values that determine which strings to return in the set value.

mysql> SELECT BIN(11) AS 'Binary 11',MAKE_SET(11, 'A','B','C','D');
+-----------+-------------------------------+
| Binary 11 | MAKE_SET(11, 'A','B','C','D') |
+-----------+-------------------------------+
| 1011      | A,B,D                         |
+-----------+-------------------------------+
1 row in set (0.00 sec))

Note : that the binary equivalent of 11 is 1011.

1. The first bit starting from the right of the binary number shown is 1 (or on), so the first string starting from the right of the list of strings is put into the set.
2. The second bit starting from the right of the binary number shown is 1 (or on), so the second string starting from the right of the list of strings is put into the set.
3. The third bit starting from the right of the binary number shown is 0 (or off), so the third string starting from the right of the list of strings is not put into the set.
4. The fourth bit counting from the right is 1, so the fourth string of the list of strings is added to the set.

Example 2 :

mysql> SELECT BIN(7) AS 'Binary 1',MAKE_SET(7, 'A','B','C','D');
+----------+------------------------------+
| Binary 1 | MAKE_SET(7, 'A','B','C','D') |
+----------+------------------------------+
| 111 | A,B,C |
+----------+------------------------------+
1 row in set (0.00 sec)

Note : The binary equivalent of 7 is 111. The first bit starting from the right of the binary number shown is 1 (or on), so the first string starting from the right of the list of strings is put into the set. The second and third bits of the binary number are also 1, so the second and third strings (i.e., B and C) are added to the set.

MAKE_SET() Example 2 : Some more examples

Below are the example to show some more examples.

mysql> SELECT BIN(4) AS 'Binary 4',MAKE_SET(4, 'A','B','C','D');
+----------+------------------------------+
| Binary 4 | MAKE_SET(4, 'A','B','C','D') |
+----------+------------------------------+
| 100      | C                            |
+----------+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT BIN(8) AS 'Binary 8',MAKE_SET(1000, 'A','B','C','D');
+----------+---------------------------------+
| Binary 8 | MAKE_SET(1000, 'A','B','C','D') |
+----------+---------------------------------+
| 1000     | D                               |
+----------+---------------------------------+
1 row in set (0.00 sec)

LOCATE() Example 3 : Multiple Binary Values

You can use a pipe in order to pass multiple binary values in the first argument:

mysql> SELECT BIN(4|2),MAKE_SET( 4 | 2 , 'A','B','C','D');
+----------+------------------------------------+
| BIN(4|2) | MAKE_SET( 4 | 2 , 'A','B','C','D') |
+----------+------------------------------------+
| 110      | B,C                                |
+----------+------------------------------------+
1 row in set (0.00 sec)

Note that you’ll get the same result if you reverse the binary values in the first argument:

mysql> SELECT BIN(2|4),MAKE_SET( 2 | 4 , 'A','B','C','D');
+----------+------------------------------------+
| BIN(2|4) | MAKE_SET( 2 | 4 , 'A','B','C','D') |
+----------+------------------------------------+
| 110      | B,C                                |
+----------+------------------------------------+
1 row in set (0.00 sec)

LIKE() Example 4 : NULL arguments

Any strings with NULL values are not appended to the result. See below example

mysql> SELECT BIN(2|4),MAKE_SET( 2 | 4 , 'A',NULL,'C','D');
+----------+-------------------------------------+
| BIN(2|4) | MAKE_SET( 2 | 4 , 'A',NULL,'C','D') |
+----------+-------------------------------------+
| 110      | C                                   |
+----------+-------------------------------------+
1 row in set (0.05 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : SUBSTR(), SUBSTR_COUNT(), SUBSTR_COMPARE(), SUBSTR_REPLACE(), PHP STRING FUNCTIONS().