	©	SQL :: Manager
				Home	Program Information	Technical details	Free download	Donate for a cup of coffee

The EXPORT_SET SQL Function in MySQL and MariaDB - Export bits to string

EXPORT_SET	Syntax:	EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits])
	Return value:	CHAR
	Function type:	String function

Function Description

The EXPORT_SET() function returns a string containing an "On" string for each set bit in "Bits" and an "Off" string for each unset bit, separated by a separator.

The bits of the "Bits" argument are evaluated from right to left, from least significant bits to most significant bits.

The "On" and "Off" indications are appended to the result in reverse order from left to right, separated by "Separator" if specified or by its default value, a comma.

The number of bits examined can optionally be specified by "Number of bits". The default is 64 if not specified.

"Number of bits" can have a maximum value of 64 (BIGINT) and is truncated if greater.

"Number of Bits" is treated as an unsigned integer, so a value of −1 is effectively 64.

If the "Bits" argument has an invalid value, it is interpreted as 0 and the function returns only "Off" values.

If any of the arguments are NULL, the EXPORT_SET() function returns NULL.

SQL Examples for the EXPORT_SET function


select export_set(15, 1, 0);
select export_set(9, 3, '');
select export_set(11923, 1, 0, '', 16), bin(11923);
select export_set(5255, 'A', 'B', '-', 16);
select export_set('5', 1, 0, '-', 16);
select export_set('x5', 1, 0, '-', 16);
select export_set('', 1, 0, '-', 16);

select export_set(null, 1, 0, '-', 16);
select export_set(5, null, 0, '-', 16);
select export_set(5, 1, null, '-', 16);
select export_set(5, 1, 0, null, 16);
select export_set(5, 1, 0, '-', null);

export_set(15, 1, 0)
varchar(127) BINARY
1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

export_set(9, 3, '')
varchar(127) BINARY
3,,,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

export_set(11923, 1, 0, '', 16)

bin(11923)

varchar(64) BINARY

1100100101110100

10111010010011

export_set(5255, 'A', 'B', '-', 16)
varchar(127) BINARY
A-A-A-B-B-B-B-A-B-B-A-B-A-B-B-B

export_set('5', 1, 0, '-', 16)
varchar(127) BINARY
1-0-1-0-0-0-0-0-0-0-0-0-0-0-0-0

export_set('x5', 1, 0, '-', 16)
varchar(127) BINARY
0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0

export_set('', 1, 0, '-', 16)
varchar(127) BINARY
0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0

export_set(null, 1, 0, '-', 16)
varchar(127) BINARY
NULL

export_set(5, null, 0, '-', 16)
varchar(127) BINARY
NULL

export_set(5, 1, null, '-', 16)
varchar(127) BINARY
NULL

export_set(5, 1, 0, null, 16)
varchar(64) BINARY
NULL

export_set(5, 1, 0, '-', null)
varchar(127) BINARY
NULL

The examples were created with the MyWAY SQL manager: Download

How to use the EXPORT_SET() function in MySQL and MariaDB databases

The EXPORT_SET() function in MySQL and MariaDB can be used to get a bitmask value as a series of characters based on bits that are set or not. This can be useful in applications such as presenting permissions, options, or flags in a more readable format.

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

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

MAKE_SET	MAKE_SET(Bits, Strings ('str1','str2', ...))
	More about MAKE_SET 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

Numeric functions	String functions	Regular Expressions	Date and Time functions
Comparison functions	Encryption & Compression	Conversion functions	NULL functions
Aggregate functions	Window functions	JSON functions	Geometric functions
Sequence functions	Information functions	Dynamic Columns	Miscellaneous functions

More information about the EXPORT_SET SQL function: mysql.com and mariadb.com

Updated: 2023-09-17

2020-2024 Heino Cunze-Fischer