Functions for working with IPv4 and IPv6 addresses

```
┌─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**

- `x` — An IP address. [`String`](/docs/sql-reference/data-types/string) or [`UInt128`](/docs/sql-reference/data-types/int-uint)

**Returned value**

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

**Examples**

**Usage example**

```
WITH '2001:438:ffff::407d:1bc1' AS IPv6_string
SELECT
    hex(IPv6StringToNum(IPv6_string)),
    hex(toIPv6(IPv6_string));
```

```
┌─hex(IPv6StringToNum(IPv6_string))─┬─hex(toIPv6(IPv6_string))─────────┐
│ 20010438FFFF000000000000407D1BC1  │ 20010438FFFF000000000000407D1BC1 │
└───────────────────────────────────┴──────────────────────────────────┘
```

**IPv4-to-IPv6 mapping**

```
SELECT toIPv6('127.0.0.1');
```

```
┌─toIPv6('127.0.0.1')─┐
│ ::ffff:127.0.0.1    │
└─────────────────────┘
```

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

Introduced in: v22.3.0

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

**Syntax**

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

**Arguments**

- `string` — IP address string to convert. - `default` — Optional. The value to return if string has an invalid format.

**Returned value**

Returns the IPv6 address, otherwise `::` or the provided optional default if argument `string` has an invalid format. [`IPv6`](/docs/sql-reference/data-types/ipv6)

**Examples**

**Valid and invalid IPv6 strings**

```
WITH
    '2001:0db8:85a3:0000:0000:8a2e:0370:7334' AS valid_IPv6_string,
    '2001:0db8:85a3::8a2e:370g:7334' AS invalid_IPv6_string,
    'not_an_ipv6' AS malformed_string
SELECT
    toIPv6OrDefault(valid_IPv6_string) AS valid,
    toIPv6OrDefault(invalid_IPv6_string) AS default_value,
    toIPv6OrDefault(malformed_string, toIPv6('::1')) AS provided_default;
```

```
┌─valid──────────────────────────────────┬─default_value─┬─provided_default─┐
│ 2001:db8:85a3::8a2e:370:7334           │ ::            │ ::1              │
└────────────────────────────────────────┴───────────────┴──────────────────┘
```

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

Introduced in: v22.3.0

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

Supported arguments:

- String representations of IPv6 addresses in standard notation.
- String representations of IPv4 addresses (converted to IPv4-mapped IPv6).
- Binary representations of IPv6 addresses.

Unsupported arguments (return `NULL`):

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

**Syntax**

```
toIPv6OrNull(x)
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```
SELECT
    toIPv6OrNull('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS valid_ipv6,
    toIPv6OrNull('invalid::ip') AS invalid_ipv6
```

```
┌─valid_ipv6──────────────────────────┬─invalid_ipv6─┐
│ 2001:db8:85a3::8a2e:370:7334        │         ᴺᵁᴸᴸ │
└─────────────────────────────────────┴──────────────┘
```

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

Introduced in: v23.1.0

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

Supported arguments:

- String representations of IPv6 addresses in standard notation.
- String representations of IPv4 addresses (converted to IPv4-mapped IPv6).
- Binary representations of IPv6 addresses.

Unsupported arguments (return zero IPv6):

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

**Syntax**

```
toIPv6OrZero(x)
```

**Arguments**

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

**Returned value**

Returns an IPv6 address if successful, otherwise zero IPv6 address (`::`). [`IPv6`](/docs/sql-reference/data-types/ipv6)

**Examples**

**Usage example**

```
SELECT
    toIPv6OrZero('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS valid_ipv6,
    toIPv6OrZero('invalid::ip') AS invalid_ipv6
```

```
┌─valid_ipv6──────────────────────────┬─invalid_ipv6─┐
│ 2001:db8:85a3::8a2e:370:7334        │ ::           │
└─────────────────────────────────────┴──────────────┘
```

- [IPv4CIDRToRange](#IPv4CIDRToRange)- [IPv4NumToString](#IPv4NumToString)- [IPv4NumToStringClassC](#IPv4NumToStringClassC)- [IPv4StringToNum](#IPv4StringToNum)- [IPv4StringToNumOrDefault](#IPv4StringToNumOrDefault)- [IPv4StringToNumOrNull](#IPv4StringToNumOrNull)- [IPv4ToIPv6](#IPv4ToIPv6)- [IPv6CIDRToRange](#IPv6CIDRToRange)- [IPv6NumToString](#IPv6NumToString)- [IPv6StringToNum](#IPv6StringToNum)- [IPv6StringToNumOrDefault](#IPv6StringToNumOrDefault)- [IPv6StringToNumOrNull](#IPv6StringToNumOrNull)- [cutIPv6](#cutIPv6)- [isIPAddressInRange](#isIPAddressInRange)- [isIPv4String](#isIPv4String)- [isIPv6String](#isIPv6String)- [toIPv4](#toIPv4)- [toIPv4OrDefault](#toIPv4OrDefault)- [toIPv4OrNull](#toIPv4OrNull)- [toIPv4OrZero](#toIPv4OrZero)- [toIPv6](#toIPv6)- [toIPv6OrDefault](#toIPv6OrDefault)- [toIPv6OrNull](#toIPv6OrNull)- [toIPv6OrZero](#toIPv6OrZero)

Was this page helpful?