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.

Syntax

sa_get_bits( bit_string [ , only_on_bits ] )

Arguments

Result set

Remarks

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.

Permissions

None

Side effects

None

See also

• sa_split_list system procedure
• SET_BIT function [Bit array]
• SET_BITS function [Aggregate]
• GET_BIT function [Bit array]

Examples

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;