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.
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.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;
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 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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
25 | |
17 | |
13 | |
8 | |
7 | |
7 | |
6 | |
6 | |
6 |