MAKE_SET

Description

The MAKE_SET function selects and combines strings from multiple string parameters based on a bitmask (bit). Returns a comma-separated string set containing all strings whose corresponding bit is 1.

Behavior aligns with MAKE_SET in MySQL.

Syntax

MAKE_SET(<bit>, <str1>[, <str2>, ...])

Parameters

Parameter	Description
<bit>	Bitmask value, binary bits indicate which strings to select. Type: BIGINT
<str1>, <str2>, ...	String parameters to be combined (variable arguments). Type: VARCHAR

Return Value

Returns VARCHAR type, a comma-separated string set.

Special cases:

• If <bit> is NULL, returns NULL
• If a string corresponding to a bit set to 1 is NULL, skip that string
• If corresponding bit exceeds parameter range, ignore that bit
• Binary bits count from right to left, bit 0 corresponds to the first string parameter
• When <bit> is 0, returns empty string

Examples

1. Basic usage: bit = 3 (binary 011, selects bit 0 and bit 1)

SELECT make_set(3, 'dog', 'cat', 'bird');

+-----------------------------------+
| make_set(3, 'dog', 'cat', 'bird') |
+-----------------------------------+
| dog,cat                           |
+-----------------------------------+

2. Skip NULL values: bit = 5 (binary 101, selects bit 0 and bit 2)

SELECT make_set(5, NULL, 'warm', 'hot');

+---------------------------------+
| make_set(5, NULL, 'warm', 'hot') |
+---------------------------------+
| hot                             |
+---------------------------------+

3. bit is 0: Select no strings

SELECT make_set(0, 'hello', 'world');

+--------------------------------+
| make_set(0, 'hello', 'world')  |
+--------------------------------+
|                                |
+--------------------------------+

4. NULL value handling

SELECT make_set(NULL, 'a', 'b', 'c');

+-------------------------------+
| make_set(NULL, 'a', 'b', 'c') |
+-------------------------------+
| NULL                          |
+-------------------------------+

5. Bit exceeds parameter range: bit = 15 (binary 1111, selects 4 bits, but only 2 parameters)

SELECT make_set(15, 'first', 'second');

+-------------------------------------+
| make_set(15, 'first', 'second')     |
+-------------------------------------+
| first,second                        |
+-------------------------------------+

6. UTF-8 special character support

SELECT make_set(7, 'ṭṛì', 'ḍḍumai', 'test');

+------------------------------------------+
| make_set(7, 'ṭṛì', 'ḍḍumai', 'test')    |
+------------------------------------------+
| ṭṛì,ḍḍumai,test                          |
+------------------------------------------+

Keywords

MAKE_SET