Encoding functions

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

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

Introduced in: v25.6.0

Decodes a Bech32 address string generated by either the bech32 or bech32m algorithms.

Note

Unlike the encode function, `Bech32Decode` will automatically handle padded FixedStrings.

**Syntax**

```
bech32Decode(address)
```

**Arguments**

- `address` — A Bech32 string to decode. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)

**Returned value**

Returns a tuple consisting of `(hrp, data)` that was used to encode the string. The data is in binary format. [`Tuple(String, String)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Decode address**

```
SELECT tup.1 AS hrp, hex(tup.2) AS data FROM (SELECT bech32Decode('bc1w508d6qejxtdg4y5r3zarvary0c5xw7kj7gz7z') AS tup)
```

```
bc   751E76E8199196D454941C45D1B3A323F1433BD6
```

**Testnet address**

```
SELECT tup.1 AS hrp, hex(tup.2) AS data FROM (SELECT bech32Decode('tb1w508d6qejxtdg4y5r3zarvary0c5xw7kzp034v') AS tup)
```

```
tb   751E76E8199196D454941C45D1B3A323F1433BD6
```

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

Introduced in: v25.6.0

Encodes a binary data string, along with a human-readable part (HRP), using the [Bech32 or Bech32m](https://en.bitcoin.it/wiki/Bech32) algorithms.

Note

When using the [`FixedString`](/docs/sql-reference/data-types/fixedstring) data type, if a value does not fully fill the row it is padded with null characters.
While the `bech32Encode` function will handle this automatically for the hrp argument, for the data argument the values must not be padded.
For this reason it is not recommended to use the [`FixedString`](/docs/sql-reference/data-types/fixedstring) data type for your data values unless you are
certain that they are all the same length and ensure that your `FixedString` column is set to that length as well.

**Syntax**

```
bech32Encode(hrp, data[, witver])
```

**Arguments**

- `hrp` — A String of `1 - 83` lowercase characters specifying the "human-readable part" of the code. Usually 'bc' or 'tb'. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `data` — A String of binary data to encode. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring)
- `witver` — Optional. The witness version (default = 1). An `UInt*` specifying the version of the algorithm to run. `0` for Bech32 and `1` or greater for Bech32m. [`UInt*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a Bech32 address string, consisting of the human-readable part, a separator character which is always '1', and a data part. The length of the string will never exceed 90 characters. If the algorithm cannot generate a valid address from the input, it will return an empty string. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Default Bech32m**

```
-- When no witness version is supplied, the default is 1, the updated Bech32m algorithm.
SELECT bech32Encode('bc', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'))
```

```
bc1w508d6qejxtdg4y5r3zarvary0c5xw7k8zcwmq
```

**Bech32 algorithm**

```
-- A witness version of 0 will result in a different address string.
SELECT bech32Encode('bc', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'), 0)
```

```
bc1w508d6qejxtdg4y5r3zarvary0c5xw7kj7gz7z
```

**Custom HRP**

```
-- While 'bc' (Mainnet) and 'tb' (Testnet) are the only allowed hrp values for the
-- SegWit address format, Bech32 allows any hrp that satisfies the above requirements.
SELECT bech32Encode('abcdefg', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'), 10)
```

```
abcdefg1w508d6qejxtdg4y5r3zarvary0c5xw7k9rp8r4
```

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

Introduced in: v21.8.0

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

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Type Description|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `(U)Int*` Prints bin digits 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 eight digits of every byte 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 eight binary numbers. Zero bytes are not omitted.|  |  |  |  | | --- | --- | --- | --- | | `Float*` and `Decimal` Encoded as their representation in memory. As we support little-endian architecture, they are encoded in little-endian. Zero leading/trailing bytes are not omitted.|  |  | | --- | --- | | `UUID` Encoded as big-endian order string. | | | | | | | | | | | |

**Syntax**

```
bin(arg)
```

**Arguments**

- `arg` — A value to convert to binary. [`String`](/docs/sql-reference/data-types/string) or [`FixedString`](/docs/sql-reference/data-types/fixedstring) 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 binary representation of the argument. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Simple integer**

```
SELECT bin(14)
```

```
┌─bin(14)──┐
│ 00001110 │
└──────────┘
```

**Float32 numbers**

```
SELECT bin(toFloat32(number)) AS bin_presentation FROM numbers(15, 2)
```

```
┌─bin_presentation─────────────────┐
│ 00000000000000000111000001000001 │
│ 00000000000000001000000001000001 │
└──────────────────────────────────┘
```

**Float64 numbers**

```
SELECT bin(toFloat64(number)) AS bin_presentation FROM numbers(15, 2)
```

```
┌─bin_presentation─────────────────────────────────────────────────┐
│ 0000000000000000000000000000000000000000000000000010111001000000 │
│ 0000000000000000000000000000000000000000000000000011000001000000 │
└──────────────────────────────────────────────────────────────────┘
```

**UUID conversion**

```
SELECT bin(toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0')) AS bin_uuid
```

```
┌─bin_uuid─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 01100001111100001100010000000100010111001011001100010001111001111001000001111011101001100000000001101010110100111101101110100000 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

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

Introduced in: v21.7.0

This function returns the positions (in ascending order) of the 1 bits in the binary representation of an unsigned integer.
Signed input integers are first casted to an unsigned integer.

**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]  │
└───────────────────────────┘
```