Functions for working with IPv4 and IPv6 addresses

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

**Arguments**

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

**Returned value**

Returns `1` if `string` is IPv4 address, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT addr, isIPv4String(addr)
FROM(
SELECT ['0.0.0.0', '127.0.0.1', '::ffff:127.0.0.1'] AS addr
)
ARRAY JOIN addr;
```

```
┌─addr─────────────┬─isIPv4String(addr)─┐
│ 0.0.0.0          │                  1 │
│ 127.0.0.1        │                  1 │
│ ::ffff:127.0.0.1 │                  0 │
└──────────────────┴────────────────────┘
```

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

Introduced in: v21.1.0

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

**Syntax**

```
isIPv6String(string)
```

**Arguments**

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

**Returned value**

Returns `1` if `string` is IPv6 address, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT addr, isIPv6String(addr)
FROM(SELECT ['::', '1111::ffff', '::ffff:127.0.0.1', '127.0.0.1'] AS addr)
ARRAY JOIN addr;
```

```
┌─addr─────────────┬─isIPv6String(addr)─┐
│ ::               │                  1 │
│ 1111::ffff       │                  1 │
│ ::ffff:127.0.0.1 │                  1 │
│ 127.0.0.1        │                  0 │
└──────────────────┴────────────────────┘
```

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

Introduced in: v20.1.0

Converts a string or a UInt32 form of IPv4 address to type IPv4.
It is similar to [`IPv4StringToNum`](/docs/sql-reference/functions/ip-address-functions#IPv4StringToNum) and [`IPv4NumToString`](/docs/sql-reference/functions/ip-address-functions#IPv4NumToString) functions but it supports both string and unsigned integer data types as input arguments.

**Syntax**

```
toIPv4(x)
```

**Arguments**

- `x` — An IPv4 address [`String`](/docs/sql-reference/data-types/string) or [`UInt8/16/32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an IPv4 address. [`IPv4`](/docs/sql-reference/data-types/ipv4)

**Examples**

**Usage example**

```
SELECT toIPv4('171.225.130.45');
```

```
┌─toIPv4('171.225.130.45')─┐
│ 171.225.130.45           │
└──────────────────────────┘
```

**Comparison with IPv4StringToNum and IPv4NumToString functions.**

```
WITH
    '171.225.130.45' AS IPv4_string
SELECT
    hex(IPv4StringToNum(IPv4_string)),
    hex(toIPv4(IPv4_string))
```

```
┌─hex(IPv4StringToNum(IPv4_string))─┬─hex(toIPv4(IPv4_string))─┐
│ ABE1822D                          │ ABE1822D                 │
└───────────────────────────────────┴──────────────────────────┘
```

**Conversion from an integer**

```
SELECT toIPv4(2130706433);
```

```
┌─toIPv4(2130706433)─┐
│ 127.0.0.1          │
└────────────────────┘
```

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

Introduced in: v22.3.0

Converts a string or a UInt32 form of an IPv4 address to [`IPv4`](/docs/sql-reference/data-types/ipv4) type.
If the IPv4 address has an invalid format, it returns `0.0.0.0` (0 IPv4), or the provided IPv4 default.

**Syntax**

```
toIPv4OrDefault(string[, default])
```

**Arguments**

- `string` — IP address string to convert. [`String`](/docs/sql-reference/data-types/string)
- `default` — Optional. The value to return if string is an invalid IPv4 address. [`IPv4`](/docs/sql-reference/data-types/ipv4)

**Returned value**

Returns a string converted to the current IPv4 address, or the default value if conversion fails. [`IPv4`](/docs/sql-reference/data-types/ipv4)

**Examples**

**Valid and invalid IPv4 strings**

```
WITH
    '192.168.1.1' AS valid_IPv4_string,
    '999.999.999.999' AS invalid_IPv4_string,
    'not_an_ip' AS malformed_string
SELECT
    toIPv4OrDefault(valid_IPv4_string) AS valid,
    toIPv4OrDefault(invalid_IPv4_string) AS default_value,
    toIPv4OrDefault(malformed_string, toIPv4('8.8.8.8')) AS provided_default;
```

```
┌─valid─────────┬─default_value─┬─provided_default─┐
│ 192.168.1.1   │ 0.0.0.0       │ 8.8.8.8          │
└───────────────┴───────────────┴──────────────────┘
```

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

Introduced in: v22.3.0

Converts an input value to a value of type `IPv4` but returns `NULL` in case of an error.
Like [`toIPv4`](#toIPv4) but returns `NULL` instead of throwing an exception on conversion errors.

Supported arguments:

- String representations of IPv4 addresses in dotted decimal notation.
- Integer representations of IPv4 addresses.

Unsupported arguments (return `NULL`):

- Invalid IP address formats.
- IPv6 addresses.
- Out-of-range values.
- Malformed addresses.

**Syntax**

```
toIPv4OrNull(x)
```

**Arguments**

- `x` — A string or integer representation of an IPv4 address. [`String`](/docs/sql-reference/data-types/string) or [`Integer`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an IPv4 address if successful, otherwise `NULL`. [`IPv4`](/docs/sql-reference/data-types/ipv4) or [`NULL`](/docs/sql-reference/syntax#null)

**Examples**

**Usage example**

```
SELECT
    toIPv4OrNull('192.168.1.1') AS valid_ip,
    toIPv4OrNull('invalid.ip') AS invalid_ip
```

```
┌─valid_ip────┬─invalid_ip─┐
│ 192.168.1.1 │       ᴺᵁᴸᴸ │
└─────────────┴────────────┘
```

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

Introduced in: v23.1.0

Converts an input value to a value of type [IPv4](/docs/sql-reference/data-types/ipv4) but returns zero IPv4 address in case of an error.
Like [`toIPv4`](#toIPv4) but returns zero IPv4 address (`0.0.0.0`) instead of throwing an exception on conversion errors.

Supported arguments:

- String representations of IPv4 addresses in dotted decimal notation.
- Integer representations of IPv4 addresses.

Unsupported arguments (return zero IPv4):

- Invalid IP address formats.
- IPv6 addresses.
- Out-of-range values.

**Syntax**

```
toIPv4OrZero(x)
```

**Arguments**

- `x` — A string or integer representation of an IPv4 address. [`String`](/docs/sql-reference/data-types/string) or [`Integer`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns an IPv4 address if successful, otherwise zero IPv4 address (`0.0.0.0`). [`IPv4`](/docs/sql-reference/data-types/ipv4)

**Examples**

**Usage example**

```
SELECT
    toIPv4OrZero('192.168.1.1') AS valid_ip,
    toIPv4OrZero('invalid.ip') AS invalid_ip
```

```
┌─valid_ip────┬─invalid_ip─┐
│ 192.168.1.1 │ 0.0.0.0    │
└─────────────┴────────────┘
```

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

Introduced in: v20.1.0

onverts a string or a `UInt128` form of IPv6 address to [`IPv6`](/docs/sql-reference/data-types/ipv6) type.
For strings, if the IPv6 address has an invalid format, returns an empty value.
Similar to [`IPv6StringToNum`](/docs/sql-reference/functions/ip-address-functions#IPv6StringToNum) and [`IPv6NumToString`](/docs/sql-reference/functions/ip-address-functions#IPv6NumToString) functions, which convert IPv6 address to and from binary format (i.e. `FixedString(16)`).

If the input string contains a valid IPv4 address, then the IPv6 equivalent of the IPv4 address is returned.

**Syntax**

```
toIPv6(x)
```

**Arguments**