AnalyticDB:Bit functions and operators

BIT_COUNT

Syntax

bit_count(bigint x)
bit_count(double x)
bit_count(varchar x)

Description

Converts the argument to its binary representation and returns the number of bits set to 1.

Return type: BIGINT

Usage notes

• If the argument is a double, it is truncated to a bigint before the bit count is computed. For example, pi() (approximately 3.14159) is truncated to 3 (binary: 11), which has 2 bits set to 1.

Examples

SELECT bit_count(2);
-- 2 in binary is 10, which has 1 bit set to 1
+--------------+
| bit_count(2) |
+--------------+
|            1 |

SELECT bit_count(pi());
-- pi() is approximately 3.14159, truncated to 3 (binary: 11), which has 2 bits set to 1
+-----------------+
| bit_count(pi()) |
+-----------------+
|               2 |

SELECT bit_count('123');
-- '123' is cast to bigint 123 (binary: 1111011), which has 6 bits set to 1
+------------------+
| bit_count('123') |
+------------------+
|                6 |

& (bitwise AND)

Description

Returns the bitwise AND of two values. Each bit in the result is 1 only if the corresponding bits in both operands are 1.

Return type: BIGINT

Example

SELECT 12 & 15;
-- 12 in binary: 1100
-- 15 in binary: 1111
-- AND result:   1100 = 12
+---------------------+
| bitwise_and(12, 15) |
+---------------------+
|                  12 |

~ (bitwise NOT)

Description

Inverts all bits of the operand.

Return type: BIGINT

Example

SELECT 2 & ~1;
-- ~1 inverts all bits of 1, then AND with 2
+--------------------------------+
| bitwise_and(2, bitwise_not(1)) |
+--------------------------------+
|                              2 |

| (bitwise OR)

Description

Returns the bitwise OR of two values. Each bit in the result is 1 if at least one of the corresponding bits in the operands is 1.

Return type: BIGINT

Example

SELECT 29 | 15;
-- 29 in binary: 11101
-- 15 in binary: 01111
-- OR result:    11111 = 31
+--------------------+
| bitwise_or(29, 15) |
+--------------------+
|                 31 |

^ (bitwise XOR)

Description

Returns the bitwise XOR of two values. Each bit in the result is 1 if the corresponding bits in the operands differ.

Return type: BIGINT

Example

SELECT 1 ^ 10;
-- 1  in binary: 0001
-- 10 in binary: 1010
-- XOR result:   1011 = 11
+--------------------+
| bitwise_xor(1, 10) |
+--------------------+
|                 11 |

>> (BITWISE_RIGHT_SHIFT)

Syntax

bitwise_right_shift(bigint x, bigint y)
bitwise_right_shift(double x, double y)
bitwise_right_shift(varchar x, varchar y)

Description

Right-shifts x by y bits. Bits shifted beyond the least-significant position are discarded.

Return type: BIGINT

Usage notes

• double and varchar arguments are truncated to bigint before the shift.

Examples

SELECT 3 >> 2;
-- 3 in binary: 11; right-shifting by 2 discards all bits, result is 0
+---------------------------+
| bitwise_right_shift(3, 2) |
+---------------------------+
|                         0 |

SELECT 3.4 >> 23.2;
-- 3.4 and 23.2 are truncated to 3 and 23; 3 >> 23 discards all bits, result is 0
+--------------------------------+
| bitwise_right_shift(3.4, 23.2) |
+--------------------------------+
|                              0 |

<< (BITWISE_LEFT_SHIFT)

Syntax

bitwise_left_shift(bigint x, bigint y)
bitwise_left_shift(double x, double y)
bitwise_left_shift(varchar x, varchar y)

Description

Left-shifts x by y bits. Zeros fill the vacated bit positions on the right.

Return type: BIGINT

Usage notes

• double and varchar arguments are truncated to bigint before the shift.

Examples

SELECT 3 << 2;
-- 3 in binary: 11; left-shifting by 2 gives 1100 = 12
+--------------------------+
| bitwise_left_shift(3, 2) |
+--------------------------+
|                       12 |

SELECT '3' << '2';
-- '3' and '2' are cast to bigint 3 and 2; result is 12
+------------------------------+
| bitwise_left_shift('3', '2') |
+------------------------------+
|                           12 |

SELECT 3.4 << 23.2;
-- 3.4 and 23.2 are truncated to 3 and 23; 3 << 23 = 25165824
+-------------------------------+
| bitwise_left_shift(3.4, 23.2) |
+-------------------------------+
|                      25165824 |