MySQL MAKE_SET() Function

By /
MAKE SET()

In this tutorial, we will study the MySQL MAKE_SET() function. The MAKE_SET() function is used to return a set value (a string containing substrings separated by “,”) consisting of the strings that have the corresponding bit in the first argument. Confusing right? Let’s make the concept clear by looking at a few examples but first, let’s see the syntax of the function.

Syntax of MySQL MAKE_SET()

MAKE_SET (bits, string1, string2,…. stringN)Code language: SQL (Structured Query Language) (sql)

Where,

  • ‘bits’ is an expression which specifies the bit values and,
  • ‘string1, string2,…. stringN’ is the list of strings.

Examples of MySQL MAKE_SET()

Let us understand MySQL MAKE_SET() better with some basic examples. We have the following list of strings –  ‘Welcome’,’To’,’JournalDev’.

Let pass 1 as the ‘bits’ value. We will use the SELECT statement and aliases in our query. The query is – 

SELECT MAKE_SET(1, 'Welcome','To','JournalDev') AS Result;Code language: SQL (Structured Query Language) (sql)

The function converts the ‘bits’ value to binary. The binary value of 1 is 1. So for the rightmost bit, the function returns ‘Welcome’. Since no other bits (obtained from the first argument) are available, the function does not add anything with ‘Welcome’. The final output is as follows –

Make Set Basic Example 1

Let us take a look at another example. This time, we will pass 2 as the ‘bits’ value. The query is –

SELECT MAKE_SET(2, 'Welcome','To','JournalDev') AS Result;Code language: SQL (Structured Query Language) (sql)

The binary of 2 is 10. The MySQL MAKE_SET() function only returns that value whose bit value is 1. So from the above list, ‘Welcome’ is assigned bit 0 and ‘To’ is assigned bit 1. A better infographic for this is shown below.

MySQL MAKE_SET Basic Example 2 Info

And the output is –

MySQL MAKE_SET Basic Example 2

Let’s keep going. This time, let us pass 4 as the ‘bits’ value. The query is –

SELECT MAKE_SET(4, 'Welcome','To','JournalDev') AS Result;Code language: SQL (Structured Query Language) (sql)

The binary value of 4 is 100. So starting from right to left, the first 0 bit is assigned to ‘Welcome’. The second 0 bit is assigned to ‘To’ and the 1 bit is assigned to ‘JournalDev’ and therefore, we get the output –

Make Set Basic Example 4

And infographic of this example is as follows –

MySQL MAKE_SET Basic Example 4 Info

MySQL MAKE_SET() With 2 Bits

We can specify the bits parameter as ‘2|4’. Let us see an example.

SELECT MAKE_SET(2|4, 'Welcome','To','JournalDev') AS Result;Code language: SQL (Structured Query Language) (sql)

Here, 2 is converted to binary to give 10. This will return the string ‘To’. Next, 4 is converted to binary to give 100. This will return the string ‘JournalDev’ and the final output looks like –

Make Set Two Bits

Working With Tables

Consider the below ‘Students’ table.

Make Set Students Table
Students Table

Now, let me execute the below query so that the examples may look understandable.

SELECT FirstName, LastName, 'FROM', City FROM Students;Code language: SQL (Structured Query Language) (sql)

And the output is –

Make Set Table Example 1

So we are going to pass the FirstName column, the LastName column, a string ‘FROM’ and the City column from the Students table as our list of strings for the MAKE_SET() function. Let us start an easy example first. Let us pass the ‘bits’ value as 13. The query is –

SELECT MAKE_SET(13, FirstName, LastName, 'FROM', City) AS Result FROM Students;Code language: SQL (Structured Query Language) (sql)

The binary for 13 is 1101. Below is an infographic as to which bits will get selected.

Make Set Table Example 3 Info

And the output is –

Make Set Table Example 3

Finally, let us see an example by passing ‘2|9’ in the bits parameter. The query is –

SELECT MAKE_SET(2|9, FirstName, LastName, 'FROM', City) AS Result FROM Students;Code language: SQL (Structured Query Language) (sql)

The binary of 2 is 10. This will return only the LastName column values. The binary of 9 is 1001. This will return only the first and the last values of the list i.e., the FirstName and the City column values. The output is –

Make Set Table Example 4

Conclusion

That is all for the MySQL MAKE_SET() function. It is quite a fun function to play around with so I encourage you to try different bit values for this function. I would also encourage you to check the below reference link.

References