A bit string <data type> is defined by a descriptor that contains two pieces of information:

  1. The <data type>'s name: either BIT or BIT VARYING.
  2. The <data type>'s length in bits.

BIT:

The required syntax for a BIT <data type> specification is:

BIT <data type> ::=
BIT [ (length) ]

BIT is a fixed length bit string, exactly "length" bits long; it defines a set of bit string values that are any correctly sized string of bits, e.g.: 10110001

The optional length, if specified, is an unsigned integer that defines the fixed length of acceptable values. The minimum length and the default length are both 1. For example, these two <data type> specifications are equivalent: both define a set of bit string values that must be exactly 1 bit long:

BIT
BIT(1)

[NON-PORTABLE] The maximum length for BIT is non-standard because the SQL Standard requires implementors to define BIT's maximum length. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the length of BIT to range from 1 to 32768 (i.e.: 4096*8 bits).

TIP: Always specify a bit length which is divisible by 8.

When operating on a BIT <data type>, you can use either binary <bit string literal>s or hexadecimal <bit string literal>s. For example, these two <literal>s represent the same bit value:

X'44'
B'01000100'

TIP: Use hexadecimal <bit string literal>s rather than binary <bit string literal>s whenever bit length is divisible by 4.

If you want to restrict your code to Core SQL, don't define any BIT <data type>s.

BIT VARYING:

The required syntax for a BIT VARYING <data type> specification is:

BIT VARYING <data type> ::=
BIT VARYING (length)

BIT VARYING is a variable length bit string, up to "length" bits long; it defines a set of bit string values that are any correctly sized string of bits, e.g.: 10110001

The mandatory length is an unsigned integer that defines the maximum length of acceptable values. The minimum length is 1. For example, this <data type> specification defines a set of bit string values that may be anywhere from 0 to 16 bits long:

BIT VARYING(16)

(Zero length bit strings can be stored in a BIT VARYING field.)

NON-PORTABLE] The maximum length for BIT VARYING is non-standard because the SQL Standard requires implementors to define BIT VARYING's maximum length. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the length of BIT VARYING to range from 1 to 32768 (i.e.: 4096*8 bits).

When operating on a BIT VARYING <data type>, you can use either binary <bit string literal>s or hexadecimal <bit string literal>s. For example, these two <literal>s represent the same bit value:

X'44'
B'01000100'

TIP: Use hexadecimal <bit string literal>s rather than binary <bit string literal>s whenever bit length is divisible by 4.

If you want to restrict your code to Core SQL, don't define any BIT VARYING <data type>s.

Now that we've described SQL's bit <data type>s, let's look at some example SQL statements that put them to use.

These SQL statements make a Table with two bit Columns, insert two rows, then search for any bit string equal to 01000100.

CREATE TABLE Bit_Examples (
      occurrence_bit BIT(8),
      occurrence_bitvarying BIT VARYING(8));

INSERT INTO Bit_Examples (
      occurrence_bit,
      occurrence_bitvarying)
      VALUES (B'11110000',X'4D');

INSERT INTO Bit_Examples (
      occurrence_bit,
      occurrence_bitvarying)
      VALUES (X'a9',B'01011010');

SELECT occurrence_bit,
       occurrence_bitvarying
FROM   Bit_Examples
WHERE  occurrence_bitvarying = X'44';

SELECT occurrence_bit,
       occurrence_bitvarying
FROM   Bit_Examples
WHERE  occurrence_bit = B'01000100';

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.

Comments

Comments loading...