Bit Functions | ClickHouse Docs

Bit functions work for any pair of types from UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64. Some functions support String and FixedString types.

The result type is an integer with bits equal to the maximum bits of its arguments. If at least one of the arguments is signed, the result is a signed number. If an argument is a floating-point number, it is cast to Int64.

bitAnd

Introduced in: v1.1.0

Performs bitwise AND operation between two values.

Syntax

bitAnd(a, b)

Arguments

a — First value. (U)Int* or Float*
b — Second value. (U)Int* or Float*

Returned value

Returns the result of bitwise operation a AND b

Examples

Usage example

CREATE TABLE bits
(
    `a` UInt8,
    `b` UInt8
)
ENGINE = Memory;

INSERT INTO bits VALUES (0, 0), (0, 1), (1, 0), (1, 1);

SELECT
    a,
    b,
    bitAnd(a, b)
FROM bits

┌─a─┬─b─┬─bitAnd(a, b)─┐
│ 0 │ 0 │            0 │
│ 0 │ 1 │            0 │
│ 1 │ 0 │            0 │
│ 1 │ 1 │            1 │
└───┴───┴──────────────┘

bitCount

Introduced in: v20.3.0

Calculates the number of bits set to one in the binary representation of a number.

Syntax

bitCount(x)

Arguments

x — An integer or float value. (U)Int* or Float*

Returned value

Returns the number of bits set to one in x. UInt8.

Examples

Usage example

SELECT bin(333), bitCount(333);

┌─bin(333)─────────┬─bitCount(333)─┐
│ 0000000101001101 │             5 │
└──────────────────┴───────────────┘

bitHammingDistance

Introduced in: v21.1.0

Returns the Hamming Distance between the bit representations of two numbers. Can be used with SimHash functions for detection of semi-duplicate strings. The smaller the distance, the more similar the strings are.

Syntax

bitHammingDistance(x, y)

Arguments

x — First number for Hamming distance calculation. (U)Int* or Float*
y — Second number for Hamming distance calculation. (U)Int* or Float*

Returned value

Returns the hamming distance between x and y UInt8

Examples

Usage example

SELECT bitHammingDistance(111, 121);

┌─bitHammingDistance(111, 121)─┐
│                            3 │
└──────────────────────────────┘

bitNot

Introduced in: v1.1.0

Performs the bitwise NOT operation.

Syntax

bitNot(a)

Arguments

a — Value for which to apply bitwise NOT operation. (U)Int* or Float* or String

Returned value

Returns the result of ~a i.e a with bits flipped.

Examples

Usage example

SELECT
    CAST('5', 'UInt8') AS original,
    bin(original) AS original_binary,
    bitNot(original) AS result,
    bin(bitNot(original)) AS result_binary;

┌─original─┬─original_binary─┬─result─┬─result_binary─┐
│        5 │ 00000101        │    250 │ 11111010      │
└──────────┴─────────────────┴────────┴───────────────┘

bitOr

Introduced in: v1.1.0

Performs bitwise OR operation between two values.

Syntax

bitOr(a, b)

Arguments

a — First value. (U)Int* or Float*
b — Second value. (U)Int* or Float*

Returned value

Returns the result of bitwise operation a OR b

Examples

Usage example

CREATE TABLE bits
(
    `a` UInt8,
    `b` UInt8
)
ENGINE = Memory;

INSERT INTO bits VALUES (0, 0), (0, 1), (1, 0), (1, 1);

SELECT
    a,
    b,
    bitOr(a, b)
FROM bits;

┌─a─┬─b─┬─bitOr(a, b)─┐
│ 0 │ 0 │           0 │
│ 0 │ 1 │           1 │
│ 1 │ 0 │           1 │
│ 1 │ 1 │           1 │
└───┴───┴─────────────┘

bitRotateLeft

Introduced in: v1.1.0

Rotate bits left by a certain number of positions. Bits that fall off wrap around to the right.

Syntax

bitRotateLeft(a, N)

Arguments

a — A value to rotate. (U)Int8/16/32/64
N — The number of positions to rotate left. UInt8/16/32/64

Returned value

Returns the rotated value with type equal to that of a. (U)Int8/16/32/64

Examples

Usage example

SELECT 99 AS a, bin(a), bitRotateLeft(a, 2) AS a_rotated, bin(a_rotated);

┌──a─┬─bin(a)───┬─a_rotated─┬─bin(a_rotated)─┐
│ 99 │ 01100011 │       141 │ 10001101       │
└────┴──────────┴───────────┴────────────────┘

bitRotateRight

Introduced in: v1.1.0

Rotate bits right by a certain number of positions. Bits that fall off wrap around to the left.

Syntax

bitRotateRight(a, N)

Arguments

a — A value to rotate. (U)Int8/16/32/64
N — The number of positions to rotate right. UInt8/16/32/64

Returned value

Returns the rotated value with type equal to that of a. (U)Int8/16/32/64

Examples

Usage example

SELECT 99 AS a, bin(a), bitRotateRight(a, 2) AS a_rotated, bin(a_rotated);

┌──a─┬─bin(a)───┬─a_rotated─┬─bin(a_rotated)─┐
│ 99 │ 01100011 │       216 │ 11011000       │
└────┴──────────┴───────────┴────────────────┘

bitShiftLeft

Introduced in: v1.1.0

Shifts the binary representation of a value to the left by a specified number of bit positions.

A FixedString or a String is treated as a single multibyte value.

Bits of a FixedString value are lost as they are shifted out. On the contrary, a String value is extended with additional bytes, so no bits are lost.

Syntax

bitShiftLeft(a, N)

Arguments

a — A value to shift. (U)Int* or String or FixedString
N — The number of positions to shift. UInt8/16/32/64

Returned value

Returns the shifted value with type equal to that of a.

Examples

Usage example with binary encoding

SELECT 99 AS a, bin(a), bitShiftLeft(a, 2) AS a_shifted, bin(a_shifted);

┌──a─┬─bin(a)───┬─a_shifted─┬─bin(a_shifted)─┐
│ 99 │ 01100011 │       140 │ 10001100       │
└────┴──────────┴───────────┴────────────────┘

Usage example with hexadecimal encoding

-- The shifted value is binary, so it is shown with `hex`.
SELECT 'abc' AS a, hex(a), hex(bitShiftLeft(a, 4)) AS a_shifted;

┌─a───┬─hex(a)─┬─a_shifted─┐
│ abc │ 616263 │ 06162630  │
└─────┴────────┴───────────┘

Usage example with Fixed String encoding

SELECT toFixedString('abc', 3) AS a, hex(a), hex(bitShiftLeft(a, 4)) AS a_shifted;

┌─a───┬─hex(a)─┬─a_shifted─┐
│ abc │ 616263 │ 162630    │
└─────┴────────┴───────────┘

bitShiftRight

Introduced in: v1.1.0

Shifts the binary representation of a value to the right by a specified number of bit positions.

A FixedString or a String is treated as a single multibyte value.

Bits of a FixedString value are lost as they are shifted out. On the contrary, a String value is extended with additional bytes, so no bits are lost.

Syntax

bitShiftRight(a, N)

Arguments

a — A value to shift. (U)Int* or String or FixedString
N — The number of positions to shift. UInt8/16/32/64

Returned value

Returns the shifted value with type equal to that of a.

Examples

Usage example with binary encoding

SELECT 101 AS a, bin(a), bitShiftRight(a, 2) AS a_shifted, bin(a_shifted);

┌───a─┬─bin(a)───┬─a_shifted─┬─bin(a_shifted)─┐
│ 101 │ 01100101 │        25 │ 00011001       │
└─────┴──────────┴───────────┴────────────────┘

Usage example with hexadecimal encoding

-- The shifted value is binary, so it is shown with `hex`.
SELECT 'abc' AS a, hex(a), hex(bitShiftRight(a, 12)) AS a_shifted;

┌─a───┬─hex(a)─┬─a_shifted─┐
│ abc │ 616263 │ 0616      │
└─────┴────────┴───────────┘

Usage example with Fixed String encoding

SELECT toFixedString('abc', 3) AS a, hex(a), hex(bitShiftRight(a, 12)) AS a_shifted;

┌─a───┬─hex(a)─┬─a_shifted─┐
│ abc │ 616263 │ 000616    │
└─────┴────────┴───────────┘

bitSlice

Introduced in: v22.2.0

Returns a substring starting with the bit from the ‘offset’ index that is ‘length’ bits long.

Syntax

bitSlice(s, offset[, length])

Arguments

s — The String or Fixed String to slice. String or FixedString
offset — Returns the starting bit position (1-based indexing).
Positive values: count from the beginning of the string.
Negative values: count from the end of the string. (U)Int8/16/32/64 or Float*
length — Optional. The number of bits to extract.
Positive values: extract length bits.
Negative values: extract from the offset to (string_length - |length|).
Omitted: extract from offset to end of string.
If length is not a multiple of 8, the result is padded with zeros on the right. (U)Int8/16/32/64 or Float*

Returned value

Returns a string containing the extracted bits, represented as a binary sequence. The result is always padded to byte boundaries (multiples of 8 bits) String

Examples

Usage example

SELECT bin('Hello'), bin(bitSlice('Hello', 1, 8));
SELECT bin('Hello'), bin(bitSlice('Hello', 1, 2));
SELECT bin('Hello'), bin(bitSlice('Hello', 1, 9));
SELECT bin('Hello'), bin(bitSlice('Hello', -4, 8));

┌─bin('Hello')─────────────────────────────┬─bin(bitSlice('Hello', 1, 8))─┐
│ 0100100001100101011011000110110001101111 │ 01001000                     │
└──────────────────────────────────────────┴──────────────────────────────┘
┌─bin('Hello')─────────────────────────────┬─bin(bitSlice('Hello', 1, 2))─┐
│ 0100100001100101011011000110110001101111 │ 01000000                     │
└──────────────────────────────────────────┴──────────────────────────────┘
┌─bin('Hello')─────────────────────────────┬─bin(bitSlice('Hello', 1, 9))─┐
│ 0100100001100101011011000110110001101111 │ 0100100000000000             │
└──────────────────────────────────────────┴──────────────────────────────┘
┌─bin('Hello')─────────────────────────────┬─bin(bitSlice('Hello', -4, 8))─┐
│ 0100100001100101011011000110110001101111 │ 11110000                      │
└──────────────────────────────────────────┴───────────────────────────────┘

bitTest

Introduced in: v1.1.0

Takes any number and converts it into binary form, then returns the value of the bit at a specified position. Counting is done right-to-left, starting at 0.

Syntax

bitTest(a, i)

Arguments

a — Number to convert. (U)Int8/16/32/64 or Float*
i — Position of the bit to return. (U)Int8/16/32/64 or Float*

Returned value

Returns the value of the bit at position i in the binary representation of a UInt8

Examples

Usage example

SELECT bin(2), bitTest(2, 1);

┌─bin(2)───┬─bitTest(2, 1)─┐
│ 00000010 │             1 │
└──────────┴───────────────┘

bitTestAll

Introduced in: v1.1.0

Returns result of the logical conjunction (AND operator) of all bits at the given positions. Counts right-to-left, starting at 0.

The logical AND between two bits is true if and only if both input bits are true.

Syntax

bitTestAll(a, index1[, index2, ... , indexN])

Arguments

a — An integer value. (U)Int8/16/32/64
index1, ... — One or multiple positions of bits. (U)Int8/16/32/64

Returned value

Returns the result of the logical conjunction UInt8

Examples

Usage example 1

SELECT bitTestAll(43, 0, 1, 3, 5);

┌─bitTestAll(43, 0, 1, 3, 5)─┐
│                          1 │
└────────────────────────────┘

Usage example 2

SELECT bitTestAll(43, 0, 1, 3, 5, 2);

┌─bitTestAll(43, 0, 1, 3, 5, 2)─┐
│                             0 │
└───────────────────────────────┘

bitTestAny

Introduced in: v1.1.0

Returns result of the logical disjunction (OR operator) of all bits at the given positions in a number. Counts right-to-left, starting at 0.

The logical OR between two bits is true if at least one of the input bits is true.

Syntax

bitTestAny(a, index1[, index2, ... , indexN])

Arguments

a — An integer value. (U)Int8/16/32/64
index1, ... — One or multiple positions of bits. (U)Int8/16/32/64

Returned value

Returns the result of the logical disjunction UInt8

Examples

Usage example 1

SELECT bitTestAny(43, 0, 2);

┌─bitTestAny(43, 0, 2)─┐
│                    1 │
└──────────────────────┘

Usage example 2

SELECT bitTestAny(43, 4, 2);

┌─bitTestAny(43, 4, 2)─┐
│                    0 │
└──────────────────────┘

bitXor

Introduced in: v1.1.0

Performs bitwise exclusive or (XOR) operation between two values.

Syntax

bitXor(a, b)

Arguments

a — First value. (U)Int* or Float*
b — Second value. (U)Int* or Float*

Returned value

Returns the result of bitwise operation a XOR b

Examples

Usage example

CREATE TABLE bits
(
    `a` UInt8,
    `b` UInt8
)
ENGINE = Memory;

INSERT INTO bits VALUES (0, 0), (0, 1), (1, 0), (1, 1);

SELECT
    a,
    b,
    bitXor(a, b)
FROM bits;

┌─a─┬─b─┬─bitXor(a, b)─┐
│ 0 │ 0 │            0 │
│ 0 │ 1 │            1 │
│ 1 │ 0 │            1 │
│ 1 │ 1 │            0 │
└───┴───┴──────────────┘