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, 335🔥, 0💬
Popular Posts:
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...