From the Archives: Using the BIT VARYING data type
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 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
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
BIT VARYING Scalar and Aggregate Functions
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’.
LONG VARBITexpression 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_XOR()are similar to
BIT_AND(), performing bit-wise OR and XOR operations respectively.
SET_BITS( integer-expression )aggregate function returns a
VARBITarray 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:
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 )
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 )
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 )
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: