Collections:
MAKE_SET() - Filtering List with Binary Set
How to filter a list of strings with a binary set provided as an integer using the MAKE_SET() function?
✍: FYIcenter.com
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
2023-12-20, 445🔥, 0💬
Popular Posts:
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
How To Convert Binary Strings into Hexadecimal Character Strings in SQL Server? When a query returns...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
What Are Out-of-Range Errors with DATETIME values in SQL Server Transact-SQL? When you enter DATETIM...