From the Archives: Using the BIT VARYING data type

[JasonHinsperger]

In this post, originally written by Glenn Paulley and posted to sybase.com in July of 2009, Glenn discusses using the VARBIT datatype in SQL Anywhere.

The ANSI/ISO SQL Standard eliminated the BIT and BIT VARYING data types with the formal adoption of SQL:2003; the last standard to support them, including the BIT_LENGTH() function, was SQL:1999. The SQL:2003 standard retained the BOOLEAN type to hold the truth values TRUE and FALSE.

The functionality provided by bit string arrays can be useful in a number of instances, and despite the deprecation of the BIT VARYING type from the SQL:2003 standard, SQL Anywhere introduced support for the BIT VARYING type in Versions 10 and up. The following type declarations are supported for bit string arrays:

• BIT VARYING [ ( length ) ]
• LONG BIT VARYING

with VARBIT as an additional shorthand for BIT VARYING. If length is unspecified, it defaults to 1. A LONG BIT VARYING column constitutes a BLOB with a maximum length of 2GB.

In SQL Anywhere, the single-valued BIT type can be used as a synonym for the SQL Standard's BOOLEAN type.

BIT VARYING Scalar and Aggregate Functions

The BIT VARYING and LONG BIT VARYING types can be manipulated using the following scalar functions:

• BIT_LENGTH()- returns the length of the bit string. Note that this function does not have the same semantics as the BIT_LENGTH() function from SQL:1992, which would return the number of bits of a character string. Hence the query
  

  SELECT BIT_LENGTH( '01101011' ); 

  returns the value 8 (the string in quotes is interpreted as a binary string), rather than 64 (using SQL:1992 semantics for BIT_LENGTH()where the string of 0's and 1's are interpreted as a character string).
• BIT_SUBSTR( bit-expression [, start [, length ] ] )- returns a substring of the bit array.
• COUNT_SET_BITS( bit-expression )- returns the number of '1' bits in the bit array.
• GET_BIT( bit-expression, position )- returns a BIT value of the bit at the specific position in the array.
• SET_BIT([ bit-expression, ]bit-position [,  value ]) - set the value of the bit at the given position to values. The default value is '0'. SET_BIT() returns a LONG VARBIT expression containing the modified bit string. If bit-expression is unspecified, the bit string defaults to a string of '0' bits of "position" length.

and by the following aggregate functions:

• BIT_AND( expression ) is an aggregate function that performs a bit-wise AND of successive bit array values from multiple rows. For example, the query
  

  SELECT BIT_AND( CAST(row_value AS VARBIT) )
  
  FROM dbo.sa_split_list('0001,0111,0100,0011') 

  returns the bit array '0000' since a bit-wise AND of the four values yields a bit string of all '0's.
• BIT_OR() and BIT_XOR() are similar to BIT_AND(), performing bit-wise OR and XOR operations respectively.
• The SET_BITS( integer-expression ) aggregate function returns a VARBIT array with bit positions set to '1' corresponding to the integer values of the expression in each row in the group. As an example, the following statements return a bit array with the 2nd, 5th, and 10th bits set to 1 (or 0100100001):
  

  CREATE TABLE T( x INTEGER );
  
  INSERT INTO T values( 2 );
  
  INSERT INTO T values( 5 );
  
  INSERT INTO T values(10 );
  
  SELECT SET_BITS( x ) FROM T;

Type conversions to BIT VARYING

With conversions from other types to the BIT VARYING type, SQL Anywhere tries as much as possible to perform intuitive conversions. A fairly complete description is available in the SQL Anywhere documentation, but here are a few examples:

• INTEGER to BIT VARYING: When converting an integer to a bit array, the length of the bit array is the number of bits in the integer type, and the bit array's value is the integer's binary representation. The most significant bit of the integer becomes the first bit of the array.
  

  SELECT CAST( CAST( 8 AS TINYINT ) AS VARBIT ) 

  returns a VARBIT(8) containing '00001000'.
• BINARY to BIT VARYING: When converting a binary type of length n to a bit array, the length of the array is n * 8 bits. The first 8 bits of the bit array become the first byte of the binary value. The most significant bit of the binary value becomes the first bit in the array. The next 8 bits of the bit array become the second byte of the binary value, and so on:
  

  SELECT CAST( 0x8181 AS VARBIT ) 

  returns a VARBIT(16) containing '1000000110000001'.
• CHAR or VARCHAR to BIT VARYING: when converting a character data type of length n to a bit array, the length of the array is n bits. Each character must be either '0' or '1' and the corresponding bit of the array is assigned the value 0 or 1.
  

  SELECT CAST( '001100' AS VARBIT )

  returns a VARBIT(6) containing '001100'.
• BIT VARYING to INTEGER: when converting a bit array to an integer data type, the bit array's binary value is interpreted according to the storage format of the integer type, using the most significant bit first.
  

  SELECT CAST( CAST( '11000010' AS VARBIT ) AS INTEGER )

  returns the integer value 194 (110000102 = 0xC2 = 194).

The sa_get_bits() system procedure

The dual of the SET_BITS() aggregate function is the system stored procedure sa_get_bits() that generates a row for each bit in a bit array, and (optionally and by default) which can generate rows for only those bit positions that are '1'. Here's an example that generates a row for each bit position in the input expression, regardless of its value: