Functions for working with IPv4 and IPv6 addresses

Introduced in: v1.1.0

Converts an IPv6 address from binary format (FixedString(16)) to its standard text representation.
IPv4-mapped IPv6 addresses are displayed in the format `::ffff:111.222.33.44`.

**Syntax**

```
IPv6NumToString(x)
```

**Aliases**: `INET6_NTOA`

**Arguments**

- `x` — IPv6 address in binary format. [`FixedString(16)`](/docs/sql-reference/data-types/fixedstring) or [`IPv6`](/docs/sql-reference/data-types/ipv6)

**Returned value**

Returns the IPv6 address string in text format. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;
```

```
┌─addr─────────┐
│ 2a02:6b8::11 │
└──────────────┘
```

**IPv6 with hits analysis**

```
SELECT
    IPv6NumToString(ClientIP6 AS k),
    count() AS c
FROM hits_all
WHERE EventDate = today() AND substring(ClientIP6, 1, 12) != unhex('00000000000000000000FFFF')
GROUP BY k
ORDER BY c DESC
LIMIT 10
```

```
┌─IPv6NumToString(ClientIP6)──────────────┬─────c─┐
│ 2a02:2168:aaa:bbbb::2                   │ 24695 │
│ 2a02:2698:abcd:abcd:abcd:abcd:8888:5555 │ 22408 │
│ 2a02:6b8:0:fff::ff                      │ 16389 │
│ 2a01:4f8:111:6666::2                    │ 16016 │
│ 2a02:2168:888:222::1                    │ 15896 │
│ 2a01:7e00::ffff:ffff:ffff:222           │ 14774 │
│ 2a02:8109:eee:ee:eeee:eeee:eeee:eeee    │ 14443 │
│ 2a02:810b:8888:888:8888:8888:8888:8888  │ 14345 │
│ 2a02:6b8:0:444:4444:4444:4444:4444      │ 14279 │
│ 2a01:7e00::ffff:ffff:ffff:ffff          │ 13880 │
└─────────────────────────────────────────┴───────┘
```

**IPv6 mapped IPv4 addresses**

```
SELECT
    IPv6NumToString(ClientIP6 AS k),
    count() AS c
FROM hits_all
WHERE EventDate = today()
GROUP BY k
ORDER BY c DESC
LIMIT 10
```

```
┌─IPv6NumToString(ClientIP6)─┬──────c─┐
│ ::ffff:94.26.111.111       │ 747440 │
│ ::ffff:37.143.222.4        │ 529483 │
│ ::ffff:5.166.111.99        │ 317707 │
│ ::ffff:46.38.11.77         │ 263086 │
│ ::ffff:79.105.111.111      │ 186611 │
│ ::ffff:93.92.111.88        │ 176773 │
│ ::ffff:84.53.111.33        │ 158709 │
│ ::ffff:217.118.11.22       │ 154004 │
│ ::ffff:217.118.11.33       │ 148449 │
│ ::ffff:217.118.11.44       │ 148243 │
└────────────────────────────┴────────┘
```

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

Introduced in: v1.1.0

Converts an IPv6 address from its standard text representation to binary format (`FixedString(16)`).
Accepts IPv4-mapped IPv6 addresses in the format `::ffff:111.222.33.44.`.
If the IPv6 address has an invalid format, an exception is thrown.

If the input string contains a valid IPv4 address, returns its IPv6 equivalent.
HEX can be uppercase or lowercase.

**Syntax**

```
IPv6StringToNum(string)
```

**Aliases**: `INET6_ATON`

**Arguments**

- `string` — IPv6 address string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns theIPv6 address in binary format. [`FixedString(16)`](/docs/sql-reference/data-types/fixedstring)

**Examples**

**Basic example**

```
SELECT addr, cutIPv6(IPv6StringToNum(addr), 0, 0) FROM (SELECT ['notaddress', '127.0.0.1', '1111::ffff'] AS addr) ARRAY JOIN addr;
```

```
┌─addr───────┬─cutIPv6(IPv6StringToNum(addr), 0, 0)─┐
│ notaddress │ ::                                   │
│ 127.0.0.1  │ ::ffff:127.0.0.1                     │
│ 1111::ffff │ 1111::ffff                           │
└────────────┴──────────────────────────────────────┘
```

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

Introduced in: v22.3.0

Converts an IPv6 address from its standard text representation to binary format (`FixedString(16)`).
Accepts IPv4-mapped IPv6 addresses in the format `::ffff:111.222.33.44.`.
If the IPv6 address has an invalid format, it returns the default value `::`.

**Syntax**

```
IPv6StringToNumOrDefault(string)
```

**Arguments**

- `string` — IPv6 address string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

IPv6 address in binary format, or zero-filled FixedString(16) if invalid. [`FixedString(16)`](/docs/sql-reference/data-types/fixedstring)

**Examples**

**Basic example with invalid address**

```
SELECT
    IPv6NumToString(IPv6StringToNumOrDefault('2001:db8::1')) AS valid,
    IPv6NumToString(IPv6StringToNumOrDefault('invalid')) AS invalid;
```

```
┌─valid───────┬─invalid─┐
│ 2001:db8::1 │ ::      │
└─────────────┴─────────┘
```

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

Introduced in: v22.3.0

Converts an IPv6 address from its standard text representation to binary format (`FixedString(16)`).
Accepts IPv4-mapped IPv6 addresses in the format `::ffff:111.222.33.44.`.
If the IPv6 address has an invalid format, it returns `NULL`.

**Syntax**

```
IPv6StringToNumOrNull(string)
```

**Arguments**

- `string` — IPv6 address string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns IPv6 address in binary format, or `NULL` if invalid. [`Nullable(FixedString(16))`](/docs/sql-reference/data-types/nullable)

**Examples**

**Basic example with invalid address**

```
SELECT
    IPv6NumToString(IPv6StringToNumOrNull('2001:db8::1')) AS valid,
    IPv6StringToNumOrNull('invalid') AS invalid;
```

```
┌─valid───────┬─invalid─┐
│ 2001:db8::1 │    ᴺᵁᴸᴸ │
└─────────────┴─────────┘
```

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

Introduced in: v1.1.0

Accepts a `FixedString(16)` value containing the IPv6 address in binary format.
Returns a string containing the address of the specified number of bytes removed in text format.

**Syntax**

```
cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4)
```

**Arguments**

- `x` — IPv6 address in binary format. [`FixedString(16)`](/docs/sql-reference/data-types/fixedstring) or [`IPv6`](/docs/sql-reference/data-types/ipv6)
- `bytesToCutForIPv6` — Number of bytes to cut for IPv6. [`UInt8`](/docs/sql-reference/data-types/int-uint)
- `bytesToCutForIPv4` — Number of bytes to cut for IPv4. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a string containing the IPv6 address in text format with specified bytes removed. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
WITH
    IPv6StringToNum('2001:0DB8:AC10:FE01:FEED:BABE:CAFE:F00D') AS ipv6,
    IPv4ToIPv6(IPv4StringToNum('192.168.0.1')) AS ipv4
SELECT
    cutIPv6(ipv6, 2, 0),
    cutIPv6(ipv4, 0, 2)
```

```
┌─cutIPv6(ipv6, 2, 0)─────────────────┬─cutIPv6(ipv4, 0, 2)─┐
│ 2001:db8:ac10:fe01:feed:babe:cafe:0 │ ::ffff:192.168.0.0  │
└─────────────────────────────────────┴─────────────────────┘
```

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

Introduced in: v21.4.0

Determines if an IP address is contained in a network represented in the [Classless Inter-Domain Routing (CIDR)](https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing) notation.

This function accepts both IPv4 and IPv6 addresses (and networks) represented as strings. It returns `0` if the IP version of the address and the CIDR don't match.

**Syntax**

```
isIPAddressInRange(address, prefix)
```

**Arguments**

- `address` — An IPv4 or IPv6 address. [`String`](/docs/sql-reference/data-types/string)
- `prefix` — An IPv4 or IPv6 network prefix in CIDR. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1` if the IP version of the address and the CIDR match, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**IPv4 address in range**

```
SELECT isIPAddressInRange('127.0.0.1', '127.0.0.0/8')
```

```
1
```

**IPv4 address not in range**

```
SELECT isIPAddressInRange('127.0.0.1', 'ffff::/16')
```

```
0
```

**IPv6 address not in range**

```
SELECT isIPAddressInRange('::ffff:192.168.0.1', '::ffff:192.168.0.4/128')
```

```
0
```

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

Introduced in: v21.1.0

Determines whether the input string is an IPv4 address or not.
For the IPv6 version see [`isIPv6String`](#isIPv6String).

**Syntax**

```
isIPv4String(string)
```