Bit functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/bit-functions.md)

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[​](#bitAnd "Direct link to bitAnd")

Introduced in: v1.1.0

Performs bitwise AND operation between two values.

**Syntax**

```
bitAnd(a, b)
```

**Arguments**

- `a` — First value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `b` — Second value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/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[​](#bitCount "Direct link to 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*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns the number of bits set to one in `x`. [`UInt8`](/docs/sql-reference/data-types/int-uint).

Note

The function does not convert the input value to a larger type ([sign extension](https://en.wikipedia.org/wiki/Sign_extension)).
For example: `bitCount(toUInt8(-1)) = 8`.

**Examples**

**Usage example**

```
SELECT bin(333), bitCount(333);
```

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

## bitHammingDistance[​](#bitHammingDistance "Direct link to bitHammingDistance")

Introduced in: v21.1.0

Returns the [Hamming Distance](https://en.wikipedia.org/wiki/Hamming_distance) between the bit representations of two numbers.
Can be used with [`SimHash`](/docs/sql-reference/functions/hash-functions#ngramSimHash) 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*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `y` — Second number for Hamming distance calculation. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns the hamming distance between `x` and `y` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT bitHammingDistance(111, 121);
```

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

## bitNot[​](#bitNot "Direct link to 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*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`String`](/docs/sql-reference/data-types/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[​](#bitOr "Direct link to bitOr")

Introduced in: v1.1.0

Performs bitwise OR operation between two values.

**Syntax**

```
bitOr(a, b)
```

**Arguments**

- `a` — First value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)
- `b` — Second value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/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[​](#bitRotateLeft "Direct link to 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`](/docs/sql-reference/data-types/int-uint)
- `N` — The number of positions to rotate left. [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the rotated value with type equal to that of `a`. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**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[​](#bitRotateRight "Direct link to 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`](/docs/sql-reference/data-types/int-uint)
- `N` — The number of positions to rotate right. [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the rotated value with type equal to that of `a`. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**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[​](#bitShiftLeft "Direct link to 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*`](/docs/sql-reference/data-types/int-uint) or [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `N` — The number of positions to shift. [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**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(99)──┬─a_shifted─┬─bin(bitShiftLeft(99, 2))─┐
│ 99 │ 01100011 │       140 │ 10001100                 │
└────┴──────────┴───────────┴──────────────────────────┘
```

**Usage example with hexadecimal encoding**

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