Encoding functions

**Syntax**

```
bitPositionsToArray(arg)
```

**Arguments**

- `arg` — An integer value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array with the ascendingly ordered positions of 1 bits in the binary representation of the input. [`Array(UInt64)`](/docs/sql-reference/data-types/array)

**Examples**

**Single bit set**

```
SELECT bitPositionsToArray(toInt8(1)) AS bit_positions
```

```
┌─bit_positions─┐
│ [0]           │
└───────────────┘
```

**All bits set**

```
SELECT bitPositionsToArray(toInt8(-1)) AS bit_positions
```

```
┌─bit_positions─────────────┐
│ [0, 1, 2, 3, 4, 5, 6, 7]  │
└───────────────────────────┘
```

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

Introduced in: v1.1.0

This function decomposes an integer into a sum of powers of two.
The powers of two are returned as an ascendingly ordered array.

**Syntax**

```
bitmaskToArray(num)
```

**Arguments**

- `num` — An integer value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an array with the ascendingly ordered powers of two which sum up to the input number. [`Array(UInt64)`](/docs/sql-reference/data-types/array)

**Examples**

**Basic example**

```
SELECT bitmaskToArray(50) AS powers_of_two
```

```
┌─powers_of_two───┐
│ [2, 16, 32]     │
└─────────────────┘
```

**Single power of two**

```
SELECT bitmaskToArray(8) AS powers_of_two
```

```
┌─powers_of_two─┐
│ [8]           │
└───────────────┘
```

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

Introduced in: v1.1.0

Like bitmaskToArray but returns the powers of two as a comma-separated string.

**Syntax**

```
bitmaskToList(num)
```

**Arguments**

- `num` — An integer value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a string containing comma-separated powers of two. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic example**

```
SELECT bitmaskToList(50) AS powers_list
```

```
┌─powers_list───┐
│ 2, 16, 32     │
└───────────────┘
```

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

Introduced in: v20.1.0

Returns a string with length equal to the number of arguments passed where each byte
has the value of the corresponding argument. Accepts multiple arguments of numeric types.

If the value of the argument is out of range of the `UInt8` data type, then it is converted
to `UInt8` with potential rounding and overflow.

**Syntax**

```
char(num1[, num2[, ...]])
```

**Arguments**

- `num1[, num2[, num3 ...]]` — Numerical arguments interpreted as integers. [`(U)Int8/16/32/64`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns a string of the given bytes. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic example**

```
SELECT char(104.1, 101, 108.9, 108.9, 111) AS hello;
```

```
┌─hello─┐
│ hello │
└───────┘
```

**Constructing arbitrary encodings**

```
-- You can construct a string of arbitrary encoding by passing the corresponding bytes.
-- for example UTF8
SELECT char(0xD0, 0xBF, 0xD1, 0x80, 0xD0, 0xB8, 0xD0, 0xB2, 0xD0, 0xB5, 0xD1, 0x82) AS hello;
```

```
┌─hello──┐
│ привет │
└────────┘
```

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

Introduced in: v1.1.0

Returns a string containing the argument's hexadecimal representation according
to the following logic for different types:

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Type Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `(U)Int*` Prints hex digits ("nibbles") from the most significant to least significant (big-endian or "human-readable" order). It starts with the most significant non-zero byte (leading zero bytes are omitted) but always prints both digits of every byte even if the leading digit is zero.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `Date` and `DateTime` Formatted as corresponding integers (the number of days since epoch for Date and the value of unix timestamp for DateTime).|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `String` and `FixedString` All bytes are simply encoded as two hexadecimal numbers. Zero bytes are not omitted.|  |  |  |  | | --- | --- | --- | --- | | `Float*` and `Decimal` Encoded as their representation in memory. ClickHouse represents the values internally always as little endian, therefore they are encoded as such. Zero leading/trailing bytes are not omitted.|  |  | | --- | --- | | `UUID` Encoded as big-endian order string. | | | | | | | | | | | |

The function uses uppercase letters `A-F` and not using any prefixes (like `0x`) or suffixes (like `h`).

**Syntax**

```
hex(arg)
```

**Arguments**

- `arg` — A value to convert to hexadecimal. [`String`](/docs/sql-reference/data-types/string) or [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal) or [`Date`](/docs/sql-reference/data-types/date) or [`DateTime`](/docs/sql-reference/data-types/datetime)

**Returned value**

Returns a string with the hexadecimal representation of the argument. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Simple integer**

```
SELECT hex(1)
```

```
01
```

**Float32 numbers**

```
SELECT hex(toFloat32(number)) AS hex_presentation FROM numbers(15, 2)
```

```
┌─hex_presentation─┐
│ 00007041         │
│ 00008041         │
└──────────────────┘
```

**Float64 numbers**

```
SELECT hex(toFloat64(number)) AS hex_presentation FROM numbers(15, 2)
```

```
┌─hex_presentation─┐
│ 0000000000002E40 │
│ 0000000000003040 │
└──────────────────┘
```

**UUID conversion**

```
SELECT lower(hex(toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0'))) AS uuid_hex
```

```
┌─uuid_hex─────────────────────────┐
│ 61f0c4045cb311e7907ba6006ad3dba0 │
└──────────────────────────────────┘
```

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

Introduced in: v24.6.0

Decodes a Hilbert curve index back into a tuple of unsigned integers, representing coordinates in multi-dimensional space.

As with the `hilbertEncode` function, this function has two modes of operation:

- **Simple**
- **Expanded**

**Simple mode**

Accepts up to 2 unsigned integers as arguments and produces a `UInt64` code.

**Expanded mode**

Accepts a range mask (tuple) as a first argument and up to 2 unsigned integers as
other arguments. Each number in the mask configures the number of bits by which
the corresponding argument will be shifted left, effectively scaling the argument
within its range.

Range expansion can be beneficial when you need a similar distribution for
arguments with wildly different ranges (or cardinality) For example: 'IP Address' `(0...FFFFFFFF)`
and 'Country code' `(0...FF)`. As with the encode function, this is limited to 8
numbers at most.

**Syntax**

```
hilbertDecode(tuple_size, code)
```

**Arguments**

- `tuple_size` — Integer value of no more than `2`. [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint) or [`Tuple(UInt8/16/32/64)`](/docs/sql-reference/data-types/tuple)
- `code` — `UInt64` code. [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple of the specified size. [`Tuple(UInt64)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Simple mode**

```
SELECT hilbertDecode(2, 31)
```

```
["3", "4"]
```

**Single argument**

```
-- Hilbert code for one argument is always the argument itself (as a tuple).
SELECT hilbertDecode(1, 1)
```

```
["1"]
```

**Expanded mode**

```
-- A single argument with a tuple specifying bit shifts will be right-shifted accordingly.
SELECT hilbertDecode(tuple(2), 32768)
```

```
["128"]
```

**Column usage**

```
-- First create the table and insert some data
CREATE TABLE hilbert_numbers(
    n1 UInt32,
    n2 UInt32
)
ENGINE=MergeTree()
ORDER BY n1 SETTINGS index_granularity_bytes = '10Mi';
insert into hilbert_numbers (*) values(1,2);