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 |
 *---------------------*/