Encoding functions

```
1572864
```

**Single argument**

```
-- Morton encoding for one argument is always the argument itself
SELECT mortonEncode(1)
```

```
1
```

**Expanded single argument**

```
SELECT mortonEncode(tuple(2), 128)
```

```
32768
```

**Column usage**

```
-- First create the table and insert some data
CREATE TABLE morton_numbers(
    n1 UInt32,
    n2 UInt32,
    n3 UInt16,
    n4 UInt16,
    n5 UInt8,
    n6 UInt8,
    n7 UInt8,
    n8 UInt8
)
ENGINE=MergeTree()
ORDER BY n1;
INSERT INTO morton_numbers (*) values(1, 2, 3, 4, 5, 6, 7, 8);

-- Use column names instead of constants as function arguments
SELECT mortonEncode(n1, n2, n3, n4, n5, n6, n7, n8) FROM morton_numbers;
```

```
2155374165
```

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

Introduced in: v24.1.0

Transforms a [sqid](https://sqids.org/) back into an array of numbers.

**Syntax**

```
sqidDecode(sqid)
```

**Arguments**

- `sqid` — The sqid to decode. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns an array of numbers from `sqid`. [`Array(UInt64)`](/docs/sql-reference/data-types/array)

**Examples**

**Usage example**

```
SELECT sqidDecode('gXHfJ1C6dN');
```

```
┌─sqidDecode('gXHfJ1C6dN')─────┐
│ [1, 2, 3, 4, 5]              │
└──────────────────────────────┘
```

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

Introduced in: v24.1.0

Transforms numbers into a [sqid](https://sqids.org/), a Youtube-like ID string.

**Syntax**

```
sqidEncode(n1[, n2, ...])
```

**Aliases**: `sqid`

**Arguments**

- `n1[, n2, ...]` — Arbitrarily many numbers. [`UInt8/16/32/64`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a hash ID [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT sqidEncode(1, 2, 3, 4, 5);
```

```
┌─sqidEncode(1, 2, 3, 4, 5)─┐
│ gXHfJ1C6dN                │
└───────────────────────────┘
```

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

Introduced in: v21.8.0

Interprets each pair of binary digits (in the argument) as a number and converts it to the byte represented by the number. The functions performs the opposite operation to bin.

For a numeric argument `unbin()` does not return the inverse of `bin()`. If you want to convert the result to a number, you can use the reverse and `reinterpretAs<Type>` functions.

Note

If `unbin` is invoked from within the `clickhouse-client`, binary strings are displayed using UTF-8.

Supports binary digits `0` and `1`. The number of binary digits does not have to be multiples of eight. If the argument string contains anything other than binary digits,
the result is undefined (no exception is thrown).

**Syntax**

```
unbin(arg)
```

**Arguments**

- `arg` — A string containing any number of binary digits. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a binary string (BLOB). [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage**

```
SELECT UNBIN('001100000011000100110010'), UNBIN('0100110101111001010100110101000101001100')
```

```
┌─unbin('001100000011000100110010')─┬─unbin('0100110101111001010100110101000101001100')─┐
│ 012                               │ MySQL                                             │
└───────────────────────────────────┴───────────────────────────────────────────────────┘
```

**Convert to number**

```
SELECT reinterpretAsUInt64(reverse(unbin('1110'))) AS num
```

```
┌─num─┐
│  14 │
└─────┘
```

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

Introduced in: v1.1.0

Performs the opposite operation of [`hex`](#hex). It interprets each pair of hexadecimal digits (in the argument) as a number and converts
it to the byte represented by the number. The returned value is a binary string (BLOB).

If you want to convert the result to a number, you can use the `reverse` and `reinterpretAs<Type>` functions.

Note

`clickhouse-client` interprets strings as UTF-8.
This may cause that values returned by `hex` to be displayed surprisingly.

Supports both uppercase and lowercase letters `A-F`.
The number of hexadecimal digits does not have to be even.
If it is odd, the last digit is interpreted as the least significant half of the `00-0F` byte.
If the argument string contains anything other than hexadecimal digits, some implementation-defined result is returned (an exception isn't thrown).
For a numeric argument the inverse of hex(N) is not performed by unhex().

**Syntax**

```
unhex(arg)
```

**Arguments**

- `arg` — A string containing any number of hexadecimal digits. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns a binary string (BLOB). [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic usage**

```
SELECT unhex('303132'), UNHEX('4D7953514C')
```

```
┌─unhex('303132')─┬─unhex('4D7953514C')─┐
│ 012             │ MySQL               │
└─────────────────┴─────────────────────┘
```

**Convert to number**

```
SELECT reinterpretAsUInt64(reverse(unhex('FFF'))) AS num
```

```
┌──num─┐
│ 4095 │
└──────┘
```

- [bech32Decode](#bech32Decode)- [bech32Encode](#bech32Encode)- [bin](#bin)- [bitPositionsToArray](#bitPositionsToArray)- [bitmaskToArray](#bitmaskToArray)- [bitmaskToList](#bitmaskToList)- [char](#char)- [hex](#hex)- [hilbertDecode](#hilbertDecode)- [hilbertEncode](#hilbertEncode)- [mortonDecode](#mortonDecode)- [mortonEncode](#mortonEncode)- [sqidDecode](#sqidDecode)- [sqidEncode](#sqidEncode)- [unbin](#unbin)- [unhex](#unhex)

Was this page helpful?