Takes a bit string and returns a row for each bit in the string. By default, only rows with a bit value of 1 are returned.
sa_get_bits( bit_string [ , only_on_bits ] )
bit_string Use this LONG VARBIT parameter to specify the bit string from which to get the bits. If the bit_string parameter is NULL, no rows are returned.
only_on_bits Use this optional BIT to specify whether to return only rows with on bits (bits with the value of 1). Specify 1 (the default) to return only rows with on bits; specify 0 to return rows for all bits in the bit string.
|bitnum||UNSIGNED INT||The position of the bit described by this row. For example, the first bit in the bit string has bitnum of 1.|
|bit_val||BIT||The value of the bit at position bitnum. If only_on_bits is set to 1, this value is always 1.|
The sa_get_bits system procedure decodes a bit string, returning one row for each bit in the bit string, indicating the value of the bit. If only_on_bits is set to 1 (the default) or NULL, then only rows corresponding to on bits are returned. An optimization allows this case to be processed efficiently for long bit strings that have few on bits. If only_on_bits is set to 0, then a row is returned for each bit in the bit string.
For example, the statement
CALL sa_get_bits( '1010' ); returns the following result set, indicating on bits in positions 1 and 3 of the bit string.
The sa_get_bits system procedure can be used to convert a bit string into a relation. This can be used to join a bit string with a table, or to retrieve a bit string as a result set instead of as a single binary value. It can be more efficient to retrieve a bit string as a result set if there are a large number of 0 bits, as these do not need to be retrieved.
The following example shows how to use the sa_get_bits system procedure to encode a set of integers as a bit string, and then decode it for use in a join:
CREATE VARIABLE @s_depts LONG VARBIT; SELECT SET_BITS( DepartmentID ) INTO @s_depts FROM Departments WHERE DepartmentName like 'S%'; SELECT * FROM sa_get_bits( @s_depts ) B JOIN Departments D ON B.bitnum = D.DepartmentID;