Functions for working with IPv4 and IPv6 addresses

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

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

Introduced in: v20.1.0

Takes an IPv4 address with its Classless Inter-Domain Routing (CIDR) prefix length and returns the subnet's address range as a tuple of two IPv4 values: the first and last addresses in that subnet.
For the IPv6 version see [`IPv6CIDRToRange`](#IPv4CIDRToRange).

**Syntax**

```
IPv4CIDRToRange(ipv4, cidr)
```

**Arguments**

- `ipv4` — IPv4 address. [`IPv4`](/docs/sql-reference/data-types/ipv4) or [`String`](/docs/sql-reference/data-types/string)
- `cidr` — CIDR value. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple with two IPv4 addresses representing the subnet range. [`Tuple(IPv4, IPv4)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Usage example**

```
SELECT IPv4CIDRToRange(toIPv4('192.168.5.2'), 16);
```

```
┌─IPv4CIDRToRange(toIPv4('192.168.5.2'), 16)─┐
│ ('192.168.0.0','192.168.255.255')          │
└────────────────────────────────────────────┘
```

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

Introduced in: v1.1.0

Converts a 32-bit integer to its IPv4 address string representation in dotted decimal notation (A.B.C.D format).
Interprets the input using big-endian byte ordering.

**Syntax**

```
IPv4NumToString(num)
```

**Aliases**: `INET_NTOA`

**Arguments**

- `num` — IPv4 address as UInt32 number. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a number representing the MAC address, or `0` if the format is invalid. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
IPv4NumToString(3232235521)
```

```
192.168.0.1
```

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

Introduced in: v1.1.0

Converts a 32-bit integer to its IPv4 address string representation in dotted decimal notation (A.B.C.D format),
similar to [`IPv4NumToString`](#IPv4NumToString) but using `xxx` instead of the last octet.

**Syntax**

```
IPv4NumToStringClassC(num)
```

**Arguments**

- `num` — IPv4 address as UInt32 number. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the IPv4 address string with xxx replacing the last octet. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Basic example with aggregation**

```
SELECT
    IPv4NumToStringClassC(ClientIP) AS k,
    count() AS c
FROM test.hits
GROUP BY k
ORDER BY c DESC
LIMIT 10
```

```
┌─k──────────────┬─────c─┐
│ 83.149.9.xxx   │ 26238 │
│ 217.118.81.xxx │ 26074 │
│ 213.87.129.xxx │ 25481 │
│ 83.149.8.xxx   │ 24984 │
│ 217.118.83.xxx │ 22797 │
│ 78.25.120.xxx  │ 22354 │
│ 213.87.131.xxx │ 21285 │
│ 78.25.121.xxx  │ 20887 │
│ 188.162.65.xxx │ 19694 │
│ 83.149.48.xxx  │ 17406 │
└────────────────┴───────┘
```

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

Introduced in: v1.1.0

Converts an IPv4 address string in dotted decimal notation (A.B.C.D format) to its corresponding 32-bit integer representation. (The reverse of [`IPv4NumToString`](#IPv4NumToString)).
If the IPv4 address has an invalid format, an exception is thrown.

**Syntax**

```
IPv4StringToNum(string)
```

**Aliases**: `INET_ATON`

**Arguments**

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

**Returned value**

Returns theIPv4 address. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
IPv4StringToNum('192.168.0.1')
```

```
3232235521
```

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

Introduced in: v22.3.0

Converts an IPv4 address string in dotted decimal notation (A.B.C.D format) to its corresponding 32-bit integer representation but if the IPv4 address has an invalid format, it returns `0`.

**Syntax**

```
IPv4StringToNumOrDefault(string)
```

**Arguments**

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

**Returned value**

Returns the IPv4 address, or `0` if invalid. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Example with an invalid address**

```
SELECT
    IPv4StringToNumOrDefault('127.0.0.1') AS valid,
    IPv4StringToNumOrDefault('invalid') AS invalid;
```

```
┌──────valid─┬─invalid─┐
│ 2130706433 │       0 │
└────────────┴─────────┘
```

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

Introduced in: v22.3.0

Converts a 32-bit integer to its IPv4 address string representation in dotted decimal notation (A.B.C.D format) but if the IPv4 address has an invalid format, it returns `NULL`.

**Syntax**

```
IPv4StringToNumOrNull(string)
```

**Arguments**

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

**Returned value**

Returns the IPv4 address, or `NULL` if invalid. [`Nullable(UInt32)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Example with an invalid address**

```
SELECT
IPv4StringToNumOrNull('127.0.0.1') AS valid,
IPv4StringToNumOrNull('invalid') AS invalid;
```

```
┌──────valid─┬─invalid─┐
│ 2130706433 │    ᴺᵁᴸᴸ │
└────────────┴─────────┘
```

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

Introduced in: v1.1.0

Interprets a (big endian) 32-bit number as an IPv4 address, which is then interpreted as the corresponding IPv6 address in `FixedString(16)` format.

**Syntax**

```
IPv4ToIPv6(x)
```

**Arguments**

- `x` — IPv4 address. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Usage example**

```
SELECT IPv6NumToString(IPv4ToIPv6(IPv4StringToNum('192.168.0.1'))) AS addr;
```

```
┌─addr───────────────┐
│ ::ffff:192.168.0.1 │
└────────────────────┘
```

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

Introduced in: v20.1.0

Takes an IPv6 address with its Classless Inter-Domain Routing (CIDR) prefix length and returns the subnet's address range as a tuple of two IPv6 values: the lowest and highest addresses in that subnet.
For the IPv4 version see [`IPv4CIDRToRange`](#IPv4CIDRToRange).

**Syntax**

```
IPv6CIDRToRange(ipv6, cidr)
```

**Arguments**

- `ipv6` — IPv6 address. [`IPv6`](/docs/sql-reference/data-types/ipv6) or [`String`](/docs/sql-reference/data-types/string)
- `cidr` — CIDR value. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a tuple with two IPv6 addresses representing the subnet range. [`Tuple(IPv6, IPv6)`](/docs/sql-reference/data-types/tuple)

**Examples**

**Usage example**

```
SELECT IPv6CIDRToRange(toIPv6('2001:0db8:0000:85a3:0000:0000:ac1f:8001'), 32);
```

```
┌─IPv6CIDRToRange(toIPv6('2001:0db8:0000:85a3:0000:0000:ac1f:8001'), 32)─┐
│ ('2001:db8::','2001:db8:ffff:ffff:ffff:ffff:ffff:ffff')                │
└────────────────────────────────────────────────────────────────────────┘
```

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

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**