Bit Functions

The following bit related functions are available in Okera.

Function	Description
bitand()	Performs a bitwise AND operation on the two given integer values
bitnot()	Performs a bitwise NOT operation on the given integer value
bitor()	Performs a bitwise OR operation on the two given integer values
bitxor()	Performs a bitwise XOR operation on the two given integer values
countset()	Counts the set (or unset) number of bits in a given integer number
getbit()	Returns the bit at a specific position in a given integer number
rotateleft()	Rotates the bits in a number to the left
rotateright()	Rotates the bits in a number to the right
setbit()	Sets a specific bit in a given number
shiftleft()	Shifts the bits in a number to the left
shiftright()	Shifts the bits in a number to the right

The parameters (one or two, based on the arity of the function) can be actual field names of relations, or may be specified as an <expressions>.

Example: Using an expression as a parameter

> SELECT bitand(3+4, 3)
3

In the example, adding 3 plus 4 is 7, or 0111 in binary format. Performing an AND bitwise operation with 3, that is 0011 in binary format, will return only the bits set in both, resulting in 0011, or 3 in decimal format.

Note that the outcome of invoking these functions may depend on the parameter data types used when invoking them. In the example, the expression value of 7 is implicitly treated as a SMALLINT. When using the rotate and shift functions, the size of the data type (explicit stated or implicitly derived) is important as bits are moved within the given number of bits. The examples below demonstrate how in an 16-bit SMALLINT the shift and move results produce numbers that fit into the same data type.

The following explains the functions and statements in more detail.

• bitand(<numeric_type>, <numeric_type>), bitnot(<numeric_type>), bitor(<numeric_type>, <numeric_type>), and bitxor(<numeric_type>, <numeric_type>)

  Allows to perform bitwise operations on the given values.

  Example: Using bitwise operations to compute results

  > -- 121 = 0111 1001 and 52 = 0011 0100
  > SELECT bitand(121, 52);
  48 -- 0011 0000
  
  > SELECT bitor(121, 52);
  125 -- 0011 1101
  
  > SELECT bitxor(121, 52);
  77 -- 0100 1101
  
  > SELECT bitnot(121);
  -122 -- 1000 0110
  

• countset(<numeric_type>), getbit(<numeric_type>, INT), and setbit(<numeric_type>, INT)

  These bit related utility functions allow to count and operate on bits in numbers.

  Example: Using bit related utility functions

  > -- 121 = 0111 1001
  > SELECT countset(121);
  5
  
  > SELECT getbit(121, 1), getbit(121, 3);
  0      1
  
  > SELECT setbit(121, 1);
  123 -- 0111 1011
  

• rotateleft(<numeric_type>, INT), rotateright(<numeric_type>, INT), shiftleft(<numeric_type>, INT), and shiftright(<numeric_type>, INT)

  This group of functions allow to move the bits of a number left or right.

  The rotate functions perform a circular shift (also called rotate no carry), that is, they fill the empty bit at the left- or the rightmost position with the bit that dropped off at the other end.

  Conversely, the shift functions perform a logical shift. that is, they fill the empty bit at the left- or the rightmost position always with a 0 bit, while ignoring the bit that dropped off at the other end.

  Example: Using bit movements to modify numbers

  > -- 121 = 0111 1001
  > SELECT rotateleft(121, 3);
  -53 -- 1100 1011
  
  > SELECT rotateright(121, 2);
  94  -- 0101 1110
  
  > SELECT shiftleft(121, 3);
  -56 -- 1100 1000
  
  > SELECT shiftright(121, 2);
  30  -- 0001 1110