Bit functions in GoogleSQL

GoogleSQL for Spanner supports the following bit functions.

Function list

Name Summary
BIT_AND Performs a bitwise AND operation on an expression.
For more information, see Aggregate functions.
BIT_COUNT Gets the number of bits that are set in an input expression.
BIT_OR Performs a bitwise OR operation on an expression.
For more information, see Aggregate functions.
BIT_REVERSE Reverses the bits in an integer.
BIT_XOR Performs a bitwise XOR operation on an expression.
For more information, see Aggregate functions.

BIT_COUNT

BIT_COUNT(expression)

Description

The input, expression, must be an integer or BYTES.

Returns the number of bits that are set in the input expression. For signed integers, this is the number of bits in two's complement form.

Return Data Type

INT64

Example

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

/*-------+--------+---------------------------------------------+--------*
 | a     | a_bits | b                                           | b_bits |
 +-------+--------+---------------------------------------------+--------+
 | 0     | 0      | b""                                         | 0      |
 | 0     | 0      | b"\x00"                                     | 0      |
 | 5     | 2      | b"\x05"                                     | 2      |
 | 8     | 1      | b"\x00\x08"                                 | 1      |
 | 65535 | 16     | b"\xff\xff"                                 | 16     |
 | -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
 | -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
 | NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
 *-------+--------+---------------------------------------------+--------*/

BIT_REVERSE

BIT_REVERSE(value, preserve_sign)

Description

Takes an integer value and returns its bit-reversed version. When preserve_sign is TRUE, this function provides the same bit-reversal algorithm used in bit-reversed sequence. For more information, see Bit-reversed sequence.

If the input value is NULL, the function returns NULL.

Arguments:

  • value: The integer to bit reverse. Sequence only supports INT64.
  • preserve_sign: TRUE to exclude the sign bit, otherwise FALSE.

Return Data Type

The same data type as value.

Example

SELECT BIT_REVERSE(100, true) AS results

/*---------------------*
 | Results             |
 +---------------------+
 | 1369094286720630784 |
 *---------------------*/
SELECT BIT_REVERSE(100, false) AS results

/*---------------------*
 | Results             |
 +---------------------+
 | 2738188573441261568 |
 *---------------------*/
SELECT BIT_REVERSE(-100, true) AS results

/*----------------------*
 | Results              |
 +----------------------+
 | -7133701809754865665 |
 *----------------------*/
SELECT BIT_REVERSE(-100, false) AS results

/*---------------------*
 | Results             |
 +---------------------+
 | 4179340454199820287 |
 *---------------------*/