Bit functions

GoogleSQL 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_CAST_TO_INT32	Cast bits to an INT32 value.
BIT_CAST_TO_INT64	Cast bits to an INT64 value.
BIT_CAST_TO_UINT32	Cast bits to an UINT32 value.
BIT_CAST_TO_UINT64	Cast bits to an UINT64 value.
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_XOR	Performs a bitwise XOR operation on an expression. For more information, see Aggregate functions.

BIT_CAST_TO_INT32

BIT_CAST_TO_INT32(value)

Description

GoogleSQL supports bit casting to INT32. A bit cast is a cast in which the order of bits is preserved instead of the value those bytes represent.

The value parameter can represent:

• INT32
• UINT32

Return Data Type

INT32

Examples

SELECT BIT_CAST_TO_UINT32(-1) as UINT32_value, BIT_CAST_TO_INT32(BIT_CAST_TO_UINT32(-1)) as bit_cast_value;

/*---------------+----------------------+
 | UINT32_value  | bit_cast_value       |
 +---------------+----------------------+
 | 4294967295    | -1                   |
 +---------------+----------------------*/

BIT_CAST_TO_INT64

BIT_CAST_TO_INT64(value)

Description

GoogleSQL supports bit casting to INT64. A bit cast is a cast in which the order of bits is preserved instead of the value those bytes represent.

The value parameter can represent:

• INT64
• UINT64

Return Data Type

INT64

Example

SELECT BIT_CAST_TO_UINT64(-1) as UINT64_value, BIT_CAST_TO_INT64(BIT_CAST_TO_UINT64(-1)) as bit_cast_value;

/*-----------------------+----------------------+
 | UINT64_value          | bit_cast_value       |
 +-----------------------+----------------------+
 | 18446744073709551615  | -1                   |
 +-----------------------+----------------------*/

BIT_CAST_TO_UINT32

BIT_CAST_TO_UINT32(value)

Description

GoogleSQL supports bit casting to UINT32. A bit cast is a cast in which the order of bits is preserved instead of the value those bytes represent.

The value parameter can represent:

• INT32
• UINT32

Return Data Type

UINT32

Examples

SELECT -1 as UINT32_value, BIT_CAST_TO_UINT32(-1) as bit_cast_value;

/*--------------+----------------------+
 | UINT32_value | bit_cast_value       |
 +--------------+----------------------+
 | -1           | 4294967295           |
 +--------------+----------------------*/

BIT_CAST_TO_UINT64

BIT_CAST_TO_UINT64(value)

Description

GoogleSQL supports bit casting to UINT64. A bit cast is a cast in which the order of bits is preserved instead of the value those bytes represent.

The value parameter can represent:

• INT64
• UINT64

Return Data Type

UINT64

Example

SELECT -1 as INT64_value, BIT_CAST_TO_UINT64(-1) as bit_cast_value;

/*--------------+----------------------+
 | INT64_value  | bit_cast_value       |
 +--------------+----------------------+
 | -1           | 18446744073709551615 |
 +--------------+----------------------*/

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