Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » System Objects » System procedures » Alphabetical list of system procedures


sa_get_bits system procedure

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.

Result set
Column Data type Description
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.

bitnum bit_val
1 1
3 1

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.



Side effects


See also

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:


SELECT  SET_BITS( DepartmentID )
 INTO @s_depts
 FROM Departments
 WHERE DepartmentName like 'S%';

 FROM sa_get_bits( @s_depts ) B 
  JOIN Departments D ON B.bitnum = D.DepartmentID;