The MAKE_SET SQL Function in MySQL and MariaDB - String based on bit values |
|
| | MAKE_SET | Syntax: | MAKE_SET(Bits, Strings ('str1','str2', ...)) | | Return value: | CHAR | | Function type: | String function | |
| | The MAKE_SET() function returns a string consisting of comma-separated strings of the "Strings" list with the corresponding bit in "Bits" set.
For example, if bit 0 is set in "Bits", the first character string will be included in the result, if bit 2 is set, the third character string, and so on...
Character strings in the "Bits" argument are interpreted as integers.
If the value of "Bits" is invalid or 0, the function returns an empty string.
NULL values in arguments of the "Strings" list are not included in the result.
If the value of "Bits" is NULL, the MAKE_SET() function returns NULL. | SQL Examples for the MAKE_SET function |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
| select make_set(5, 'v1', 'v2', 'v3', 'v4');
select make_set('5', 'v1', 'v2', 'v3', 'v4');
select make_set(15, 'v1', 'v2', 'v3', 'v4');
select make_set(0x08, 'v1', 'v2', 'v3', 'v4');
select make_set(0x01, 'v1', 'v2', 'v3', 'v4');
select make_set(0b0101, 'v1', 'v2', 'v3', 'v4');
select make_set(15, 'v1',null, 'v3',null);
select make_set('x5', 'v1', 'v2', 'v3', 'v4');
select make_set(null, 'v1', 'v2', 'v3', 'v4');
|
|
| make_set(5, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v1,v3 |
|
|
| make_set('5', 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v1,v3 |
|
|
| make_set(15, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v1,v2,v3,v4 |
|
|
| make_set(0x08, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v4 |
|
|
| make_set(0x01, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v1 |
|
|
| make_set(0b0101, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| v1,v3 |
|
|
| make_set(15, 'v1',null, 'v3',null) |
|---|
| varchar(7) BINARY |
|---|
| v1,v3 |
|
|
| make_set('x5', 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| |
|
|
| make_set(null, 'v1', 'v2', 'v3', 'v4') |
|---|
| varchar(11) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the MAKE_SET() function in MySQL and MariaDB databases | | The MAKE_SET() function in MySQL and MariaDB is used to set a set of values based on to create a bitmask. The function returns a string in which the set elements are concatenated with commas. The position of each element in the result corresponds to a bit position in the bitmask. If a bit is set (1) in the bit mask, the corresponding element is included in the result. If a bit is not set (0), the corresponding element is excluded. The MAKE_SET() function is useful when a set of values should be rendered based on a bitmask, for example to show options or categories that are chosen based on certain flags or conditions. | | Further MySQL und MariaDB SQL String functions | | CHAR | CHAR(Character code, [, Charset]) | | More about CHAR Function |
| | CHR | CHR(Character code) | | More about CHR Function |
| | CONCAT | CONCAT(Strings ('str1','str2', ...)) | | More about CONCAT Function |
| | CONCAT_WS | CONCAT_WS(Separator, Strings ('str1','str2', ...)) | | More about CONCAT_WS Function |
| | ELT | ELT(Index, Strings ('str1','str2', ...)) | | More about ELT Function |
| | EXPORT_SET | EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits]) | | More about EXPORT_SET Function |
| | EXTRACTVALUE | EXTRACTVALUE(XML, XPath) |
| | FIELD | FIELD(Search string, Strings ('str1','str2', ...)) | | More about FIELD Function |
| | FORMAT | FORMAT(Number, Decimals [, Region]) | | More about FORMAT Function |
| | INSERT | INSERT(String, Position, Length, String to insert) | | More about INSERT Function |
| | LCASE | LCASE(String) | | More about LCASE Function |
| | LEFT | LEFT(String, Length) | | More about LEFT Function |
| | LOAD_FILE | LOAD_FILE(File name) |
| | LOWER | LOWER(String) | | More about LOWER Function |
| | LPAD | LPAD(String, Length, String to append) | | More about LPAD Function |
| | LPAD_ORACLE | LPAD_ORACLE(String, Length, String to append) | | More about LPAD_ORACLE Function |
| | LTRIM | LTRIM(String) | | More about LTRIM Function |
| | MID | MID(String, Position [, Length]) | | More about MID Function |
| | QUOTE | QUOTE(String) | | More about QUOTE Function |
| | REPEAT | REPEAT(String, Number) | | More about REPEAT Function |
| | REPLACE | REPLACE(String, Search, Replace) | | More about REPLACE Function |
| | REVERSE | REVERSE(String) | | More about REVERSE Function |
| | RIGHT | RIGHT(String, Length) | | More about RIGHT Function |
| | RPAD | RPAD(String, Length, String to append) | | More about RPAD Function |
| | RPAD_ORACLE | RPAD_ORACLE(String, Length, String to append) | | More about RPAD_ORACLE Function |
| | RTRIM | RTRIM(String) | | More about RTRIM Function |
| | SOUNDEX | SOUNDEX(String) | | More about SOUNDEX Function |
| | SPACE | SPACE(Number) | | More about SPACE Function |
| | SUBSTR | SUBSTR(String, Position, Length) | | More about SUBSTR Function |
| | SUBSTRING | SUBSTRING(String, Position, Length) | | More about SUBSTRING Function |
| | SUBSTRING_INDEX | SUBSTRING_INDEX(String, Delimiter, Number) | | More about SUBSTRING_INDEX Function |
| | TRIM | TRIM([Type] [, Delete character], , String) | | More about TRIM Function |
| | TRIM_ORACLE | TRIM_ORACLE([Type] [, Delete character], , String) | | More about TRIM_ORACLE Function |
| | UCASE | UCASE(String) | | More about UCASE Function |
| | UPPER | UPPER(String) | | More about UPPER Function |
| | WEIGHT_STRING | WEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting]) | | More about WEIGHT_STRING Function |
| | ASCII | ASCII(String) | | More about ASCII Function |
| | BIT_LENGTH | BIT_LENGTH(String) | | More about BIT_LENGTH Function |
| | CHAR_LENGTH | CHAR_LENGTH(String) | | More about CHAR_LENGTH Function |
| | CHARACTER_LENGTH | CHARACTER_LENGTH(String) | | More about CHARACTER_LENGTH Function |
| | FIND_IN_SET | FIND_IN_SET(Search string, Set of values) | | More about FIND_IN_SET Function |
| | INSTR | INSTR(String, Search string) | | More about INSTR Function |
| | LENGTH | LENGTH(String) | | More about LENGTH Function |
| | LENGTHB | LENGTHB(String) | | More about LENGTHB Function |
| | LOCATE | LOCATE(Search string, String [, Position]) | | More about LOCATE Function |
| | OCTET_LENGTH | OCTET_LENGTH(String) | | More about OCTET_LENGTH Function |
| | ORD | ORD(String) | | More about ORD Function |
| | POSITION | POSITION(Search string IN String) | | More about POSITION Function |
|
|
| | | More information about the MAKE_SET SQL function: and |
|
|
|
|
