MAKE_SET() - Filtering List with Binary Set

Q

How to filter a list of strings with a binary set provided as an integer using the MAKE_SET() function?

✍: FYIcenter.com

A

MAKE_SET(bits, str1, str2, ...) is a MySQL built-in function that filters a list of strings with a binary set provided as an integer. It loops through every bit of the given integer "bits" starting from the lowest bit and takes the string argument at the corresponding position if on only if the bit value is 1. The output is concatenated with "," as delimiters. For example:

SELECT MAKE_SET(1, 'a','b','c'), MAKE_SET(2, 'a','b','c'), MAKE_SET(4, 'a','b','c');
  -- +--------------------------+--------------------------+--------------------------+
  -- | MAKE_SET(1, 'a','b','c') | MAKE_SET(2, 'a','b','c') | MAKE_SET(4, 'a','b','c') |
  -- +--------------------------+--------------------------+--------------------------+
  -- | a                        | b                        | c                        |
  -- +--------------------------+--------------------------+--------------------------+

SELECT MAKE_SET(5, 'a','b','c'), MAKE_SET(b'101', 'a','b','c');
  -- +--------------------------+-------------------------------+
  -- | MAKE_SET(5, 'a','b','c') | MAKE_SET(b'101', 'a','b','c') |
  -- +--------------------------+-------------------------------+
  -- | a,c                      | a,c                           |
  -- +--------------------------+-------------------------------+

SELECT MAKE_SET(1 | 4,'hello','nice','world'), MAKE_SET(5,'hello','nice','world');
  -- +----------------------------------------+------------------------------------+
  -- | MAKE_SET(1 | 4,'hello','nice','world') | MAKE_SET(5,'hello','nice','world') |
  -- +----------------------------------------+------------------------------------+
  -- | hello,world                            | hello,world                        |
  -- +----------------------------------------+------------------------------------+

Note that NULL in the output will be skipped.

SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
  -- +---------------------------------------------+
  -- | MAKE_SET(1 | 4,'hello','nice',NULL,'world') |
  -- +---------------------------------------------+
  -- | hello                                       |
  -- +---------------------------------------------+

Reference information of the MAKE_SET() function:

MAKE_SET(bits, str1, str2, ...): str
  Filters a list of strings with a binary set provided as an integer.

Arguments, return value and availability:
  bits: Required. The sequence of bits given as an integer.
  str1, str2, ...: One or more strings as a string list.
  str: Return value. The concatenated string of the filtered list.
  Available since MySQL 4.

 

MOD() - Remainder of X divided by Y

LOG2() - Logarithm to Base 2

MySQL Functions on Numeric Values

⇑⇑ MySQL Function References

2023-12-20, 353🔥, 0💬