Bit functions in GoogleSQL

GoogleSQL for Spanner supports the following bit functions.

Function list

Name	Summary
BIT_COUNT	Gets the number of bits that are set in an input expression.
BIT_REVERSE	Reverses the bits in an integer.

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