IP addresses functions

IP address functions in provide a set of tools for working with both IPv4 and IPv6 addresses.

Key capabilities include:

Converting between string and numeric representations of IP addresses
Extracting network information (e.g., subnets, CIDR ranges)
Performing IP address arithmetic and comparisons
Handling both IPv4 and IPv6 address formats

These functions are particularly useful for tasks such as:

Network log analysis
Geolocation services
Access control and security applications
Network configuration management

ClickHouse functions reference

IPv4NumToString

Converts an IPv4 address from its numeric representation to a string.

Syntax

IPv4NumToString(num)

Alias:

INET_NTOA

Arguments

num (UInt32): IPv4 address as a UInt32 number in big endian format.

Returns:

String representation of the IPv4 address in A.B.C.D format.

Example

SELECT
  IPv4NumToString(2130706433) AS ip_address;

Result:

| ip_address |
|------------|
| 127.0.0.1  |

This function is useful for converting numeric IP addresses stored in databases or logs into human-readable string format, often used in network analysis or when displaying IP addresses in user interfaces.

The input is interpreted as big endian, which is the standard network byte order for IP addresses.

IPv4StringToNum

Converts an IPv4 address from string format to its numeric representation.

Syntax:

IPv4StringToNum(ip_address)

Alias:

INET_ATON

Arguments:

ip_address (String): IPv4 address in string format (A.B.C.D).

Returns:

Numeric representation of the IPv4 address. (UInt32)

Example:

SELECT
	IPv4StringToNum('192.168.0.1') AS ip_num;

Result:

| ip_num     |
|------------|
| 3232235521 |

In this example, the IPv4 address ‘192.168.0.1’ is converted to its numeric representation.

If the IPv4 address has an invalid format, the function throws an exception.

See also:

IPv4NumToString — Performs the reverse operation.
IPv4StringToNumOrDefault — Returns 0 for invalid IPv4 addresses instead of throwing an exception.
IPv4StringToNumOrNull — Returns NULL for invalid IPv4 addresses instead of throwing an exception.

This function is useful for storing IPv4 addresses efficiently in numeric format or for performing numeric operations on IP addresses.

IPv4StringToNumOrDefault

Converts an IPv4 address from a string to its numeric representation.

Syntax:

IPv4StringToNumOrDefault(ip_address)

Arguments:

ip_address (String): The IPv4 address in string format (e.g., ‘192.168.0.1’).

Returns:

A UInt32 value representing the IPv4 address.
If the input string is not a valid IPv4 address, it returns 0.

Example:

SELECT
	IPv4StringToNumOrDefault('192.168.0.1') AS valid_ip,
	IPv4StringToNumOrDefault('256.0.0.1') AS invalid_ip,
	IPv4StringToNumOrDefault('taco.special') AS not_an_ip;

Result:

| valid_ip   | invalid_ip | not_an_ip |
|------------|------------|-----------|
| 3232235521 | 0          | 0         |

In this example:

‘192.168.0.1’ is converted to its numeric representation.
‘256.0.0.1’ is invalid (256 is out of range for an octet), so it returns 0.
‘taco.special’ is not an IP address, so it returns 0.

This function is similar to IPv4StringToNum, but instead of throwing an exception for invalid input, it returns 0. This can be useful in scenarios where you want to handle invalid IP addresses gracefully without causing query errors.

IPv4StringToNumOrNull

Converts an IPv4 address from a string format to its numeric representation.

Syntax:

IPv4StringToNumOrNull(ip_address)

Arguments:

ip_address (String): The IPv4 address in string format (e.g., ‘192.168.0.1’).

Returns:

The numeric representation of the IPv4 address as a UInt32 value.
Null if the input string is not a valid IPv4 address.

Example:

SELECT
	IPv4StringToNumOrNull('192.168.0.1') AS valid_ip,
	IPv4StringToNumOrNull('256.0.0.1') AS invalid_ip,
	IPv4StringToNumOrNull('taco.special.sauce') AS not_an_ip;

Result:

| valid_ip   | invalid_ip | not_an_ip |
|------------|------------|-----------|
| 3232235521 | NULL       | NULL      |

In this example:

‘192.168.0.1’ is converted to its numeric representation.
‘256.0.0.1’ is invalid (256 is out of range for an octet) and returns NULL.
‘taco.special.sauce’ is not an IP address and returns NULL.

This function is useful when you need to convert IP addresses to their numeric form for storage or comparison, while safely handling invalid inputs by returning NULL instead of throwing an exception.

IPv4NumToStringClassC

Converts an IPv4 address from a UInt32 number to a string, replacing the last octet with ‘xxx’.

Syntax:

IPv4NumToStringClassC(num)

Arguments:

num (UInt32): IPv4 address represented as a number in big endian format.

Returns:

A string containing the IPv4 address with the last octet replaced by ‘xxx’. [String]

Example:

SELECT
	IPv4NumToStringClassC(toIPv4('192.168.0.1')) AS ip_class_c;

Result:

| ip_class_c    |
|---------------|
| 192.168.0.xxx |

This function is useful for grouping IP addresses by their Class C network. For example:

SELECT
	IPv4NumToStringClassC(ClientIP) AS ip_class_c,
	count() AS taco_orders
FROM
	taco_delivery_log
GROUP BY
	ip_class_c
ORDER BY
	taco_orders DESC
LIMIT 5;

Result:

| ip_class_c    | taco_orders |
|---------------|-------------|
| 192.168.1.xxx | 26238       |
| 10.0.0.xxx    | 26074       |
| 172.16.0.xxx  | 25481       |
| 192.168.2.xxx | 24984       |
| 10.0.1.xxx    | 22797       |

This example shows the top 5 Class C networks by number of taco orders.

The use of ‘xxx’ for the last octet is unconventional. Future versions may change this format. It’s recommended not to rely on the exact format of this fragment in production code.

IPv6NumToString

Converts an IPv6 address from binary format to a string representation.

Syntax:

IPv6NumToString(x)

Alias:

INET6_NTOA

Arguments:

x (FixedString(16)): The IPv6 address in binary format.

Returns:

A string containing the IPv6 address in text format. IPv6-mapped IPv4 addresses are output in the format ::ffff:111.222.33.44.

Examples:

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

Result:

| addr          |
|---------------|
| 2a02:6b8::11  |

SELECT
	IPv6NumToString(ClientIP6) AS ip,
	count() AS c
FROM hits_all
WHERE EventDate = today()
GROUP BY ip
ORDER BY c DESC
LIMIT 3;

Result:

| ip                      | c       |
|-------------------------|---------|
| ::ffff:94.26.111.111    | 747440  |
| ::ffff:37.143.222.4     | 529483  |
| ::ffff:5.166.111.99     | 317707  |

In this example, we convert IPv6 addresses stored in the ClientIP6 column to their string representations and count their occurrences.

IPv6-mapped IPv4 addresses are displayed with the ::ffff: prefix, as shown in the example results.

This function is particularly useful when you need to display IPv6 addresses in a human-readable format, such as in logs, user interfaces, or when preparing data for export.

IPv6StringToNum

Converts an IPv6 address from string format to its binary representation.

Syntax

IPv6StringToNum(ip_address)

Arguments

ip_address (String): IPv6 address as a string.

Returns

The IPv6 address in binary format. [FixedString(16)]

Examples

SELECT
	IPv6StringToNum('2001:0db8:ac10:fe01:feed:babe:cafe:f00d') AS ipv6_binary;

Result:

| ipv6_binary                           |
|---------------------------------------|
| 20010db8ac10fe01feedbabecafef00d      |

This function can also handle IPv4-mapped IPv6 addresses:

SELECT
	IPv6StringToNum('::ffff:192.168.0.1') AS ipv4_mapped_ipv6;

Result:

| ipv4_mapped_ipv6                      |
|---------------------------------------|
| 00000000000000000000ffffc0a80001      |

If the input string is not a valid IPv6 address, the function will throw an exception.

For IPv4 addresses, use IPv4StringToNum instead.

See Also

IPv6NumToString: The reverse function that converts binary IPv6 to string format.
toIPv6: An alias for this function that returns an IPv6 type instead of FixedString(16).

IPv6StringToNumOrDefault

Converts an IPv6 address from string format to its binary representation. If the input string is not a valid IPv6 address, it returns a default value.

Syntax

IPv6StringToNumOrDefault(ip_address)

Arguments

ip_address (String): IPv6 address as a string.

Returns

Binary representation of the IPv6 address. (FixedString(16))
If the input is not a valid IPv6 address, returns a string of 16 null bytes.

Example

SELECT
	IPv6StringToNumOrDefault('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS ipv6_binary;

Result:

| ipv6_binary                         |
|-------------------------------------|
| 20010db885a3000000008a2e03707334    |

Example with invalid input:

SELECT
	IPv6StringToNumOrDefault('Spicy Taco') AS invalid_ipv6;

Result:

| invalid_ipv6                        |
|-------------------------------------|
| 00000000000000000000000000000000    |

This function is useful when you need to convert IPv6 addresses to their binary form for storage or comparison, and you want to handle invalid inputs gracefully without throwing an exception.

If you need to distinguish between valid and invalid inputs, consider using IPv6StringToNumOrNull instead, which returns NULL for invalid IPv6 addresses.

IPv6StringToNumOrNull

Converts an IPv6 address from string format to its binary representation. If the input string is not a valid IPv6 address, the function returns NULL.

Syntax

IPv6StringToNumOrNull(ip_address)

Arguments

ip_address (String): IPv6 address as a string.

Returns

The binary representation of the IPv6 address as a FixedString(16).
NULL if the input is not a valid IPv6 address.

Example

SELECT
	IPv6StringToNumOrNull('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS ipv6_binary;

Result:

| ipv6_binary                             |
|-----------------------------------------|
| 20010db885a3000000008a2e03707334        |

Example with invalid input:

SELECT
	IPv6StringToNumOrNull('Not an IP address') AS invalid_ip;

Result:

| invalid_ip |
|------------|
| NULL       |

This function is useful when you need to convert IPv6 addresses to their binary form for storage or comparison, while handling potentially invalid input gracefully by returning NULL instead of throwing an exception.

IPv4ToIPv6

Converts an IPv4 address to its IPv6 representation.

Syntax:

IPv4ToIPv6(ip)

Arguments:

ip (UInt32): The IPv4 address as a 32-bit unsigned integer.

Returns:

The IPv6 representation of the input IPv4 address as FixedString(16).

Example:

SELECT
	IPv6NumToString(IPv4ToIPv6(IPv4StringToNum('192.168.0.1'))) AS ipv6_address;

Result:

| ipv6_address       |
|--------------------|
| ::ffff:192.168.0.1 |

In this example, we convert the IPv4 address ‘192.168.0.1’ to its IPv6 equivalent. The result is the IPv6-mapped IPv4 address.

This function is useful when working with mixed IPv4 and IPv6 environments or when you need to represent IPv4 addresses in IPv6 format.

The resulting IPv6 address always starts with the prefix ::ffff:, which is the standard prefix for IPv4-mapped IPv6 addresses.

cutIPv6

Cuts specified bytes from IPv6 and IPv4 addresses in binary format.

Syntax:

cutIPv6(address, bytesToCutForIPv6, bytesToCutForIPv4)

Arguments:

address (FixedString(16)): IPv6 address in binary format.
bytesToCutForIPv6 (UInt8): Number of bytes to cut for IPv6 addresses.
bytesToCutForIPv4 (UInt8): Number of bytes to cut for IPv4 addresses.

Returns:

A string containing the truncated IP address in text format.

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) AS truncated_ipv6,
	cutIPv6(ipv4, 0, 2) AS truncated_ipv4

Result:

| truncated_ipv6                      | truncated_ipv4     |
|-------------------------------------|--------------------|
| 2001:db8:ac10:fe01:feed:babe:cafe:0 | ::ffff:192.168.0.0 |

In this example:

For the IPv6 address, the last 2 bytes are cut off.
For the IPv4 address (mapped to IPv6), the last 2 bytes of the IPv4 part are cut off.

This function is useful for anonymizing IP addresses or for grouping them by network prefix.

IPv4CIDRToRange

Converts an IPv4 address and CIDR prefix to a range of IPv4 addresses.

Syntax:

IPv4CIDRToRange(ipv4, cidr)

Arguments:

ipv4 (IPv4 or String): The IPv4 address.
cidr (UInt8): The CIDR prefix length.

Returns:

A tuple containing two IPv4 addresses representing the lower and upper bounds of the IP range.

Example:

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

Result:

| IPv4CIDRToRange(toIPv4('192.168.5.2'), 16)         |
|----------------------------------------------------|
| ('192.168.0.0','192.168.255.255')                  |

This function is useful for determining the range of IP addresses within a given subnet. In the example, it shows that the IP address 192.168.5.2 with a CIDR prefix of 16 belongs to the range from 192.168.0.0 to 192.168.255.255.

This function works with IPv4 addresses only. For IPv6 addresses, use IPv6CIDRToRange.

IPv6CIDRToRange

Converts an IPv6 address and CIDR prefix to a range of IPv6 addresses.

Syntax:

IPv6CIDRToRange(ipv6, cidr)

Arguments:

ipv6 (IPv6 or String): The IPv6 address.
cidr (UInt8): The CIDR prefix length.

Returns:

A tuple containing two IPv6 addresses representing the lower and upper bounds of the IPv6 range.

Example:

SELECT
	IPv6CIDRToRange(toIPv6('2001:db8::1'), 32) AS range;

Result:

| range                                                           |
|-----------------------------------------------------------------|
| ('2001:db8::','2001:db8:ffff:ffff:ffff:ffff:ffff:ffff')         |

In this example, we calculate the IPv6 range for the address ‘2001:db8::1’ with a CIDR prefix of 32. The result shows the lower bound (‘2001:db8::’) and upper bound (‘2001:db8:ffff:ffff:ffff:ffff:ffff:ffff’) of the range.

This function is useful for network calculations, IP address management, and firewall rule configurations in IPv6 networks.

toIPv4

Converts a string representation of an IPv4 address to an IPv4 type.

Syntax

toIPv4(string)

Arguments

string (String) — A string containing an IPv4 address.

Returns

The IPv4 address. [IPv4]

This function is an alias to IPv4StringToNum() but returns a value of [IPv4] type instead of UInt32.

Example

SELECT
	toTypeName(toIPv4('192.168.0.1')) AS ipv4_type,
	toIPv4('192.168.0.1') AS ipv4_value;

Result:

| ipv4_type | ipv4_value  |
|-----------|-------------|
| IPv4      | 192.168.0.1 |

See Also

IPv4StringToNum
toIPv4OrDefault
toIPv4OrNull

If you need to work with the binary representation of IPv4 addresses, consider using IPv4StringToNum instead.

toIPv4OrDefault

Converts a string representation of an IPv4 address to its numeric form. If the input string is not a valid IPv4 address, it returns 0.0.0.0 (0 in IPv4 format).

Syntax

toIPv4OrDefault(string)

Arguments

string (String) — A string containing an IPv4 address.

Returns

The IPv4 address in numeric form. (IPv4)

Example

SELECT
	toIPv4OrDefault('192.168.0.1') AS valid_ip,
	toIPv4OrDefault('192.168.0.256') AS invalid_ip,
	toIPv4OrDefault('taco.special') AS not_an_ip;

Result:

| valid_ip    | invalid_ip | not_an_ip |
|-------------|------------|-----------|
| 192.168.0.1 | 0.0.0.0    | 0.0.0.0   |

In this example:

‘192.168.0.1’ is converted to its numeric IPv4 representation.
‘192.168.0.256’ is invalid (256 is out of range for an octet), so it returns 0.0.0.0.
‘taco.special’ is not an IP address at all, so it also returns 0.0.0.0.

This function is useful when you need to handle potentially invalid IP addresses in your data without causing errors, defaulting to 0.0.0.0 for any invalid input.

toIPv4OrNull

Converts a string representation of an IPv4 address to an IPv4 type. If the input string is not a valid IPv4 address, it returns NULL.

Syntax

toIPv4OrNull(string)

Arguments

string (String) — A string containing an IPv4 address.

Returns

The IPv4 address. [IPv4]
NULL if the input string is not a valid IPv4 address.

Example

SELECT
	toIPv4OrNull('192.168.0.1') AS valid_ip,
	toIPv4OrNull('192.168.0.256') AS invalid_ip,
	toIPv4OrNull('taco.special') AS not_an_ip;

Result:

| valid_ip    | invalid_ip | not_an_ip |
|-------------|------------|-----------|
| 192.168.0.1 | NULL       | NULL      |

In this example:

‘192.168.0.1’ is converted to a valid IPv4 address.
‘192.168.0.256’ is invalid (256 is out of range for an octet) and returns NULL.
‘taco.special’ is not an IP address at all and returns NULL.

This function is useful when you need to handle potentially invalid IP addresses in your data without causing errors, such as when processing user input or cleaning data from external sources.

toIPv6OrDefault

Converts a string representation of an IPv6 address to the IPv6 type. If the input string has an invalid IPv6 format, it returns the default IPv6 address (::).

Syntax

toIPv6OrDefault(string)

Arguments

string (String): A string containing an IPv6 address.

Returns

The IPv6 address. [IPv6]
If the input is invalid, returns :: (the IPv6 equivalent of 0.0.0.0).

Example

SELECT
	toIPv6OrDefault('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS valid_ipv6,
	toIPv6OrDefault('Not an IP') AS invalid_ipv6;

Result:

| valid_ipv6                    | invalid_ipv6 |
|-------------------------------|--------------|
| 2001:db8:85a3::8a2e:370:7334  | ::           |

In this example:

The valid IPv6 address is converted correctly.
The invalid input returns the default IPv6 address (::).

This function also accepts valid IPv4 addresses and returns their IPv6 equivalents:

SELECT
toIPv6OrDefault('192.168.0.1') AS ipv4_as_ipv6;

Result:

| ipv4_as_ipv6        |
|---------------------|
| ::ffff:192.168.0.1  |

This function is useful when you need to ensure all inputs are converted to valid IPv6 addresses, with invalid inputs defaulting to a known value instead of causing errors.

toIPv6OrNull

Converts a string representation of an IPv6 address to the IPv6 data type. If the input string is not a valid IPv6 address, it returns NULL.

Syntax

toIPv6OrNull(string)

Arguments

string (String): A string containing an IPv6 address.

Returns

An IPv6 address of type IPv6.
NULL if the input string is not a valid IPv6 address.

Example

SELECT
    toIPv6OrNull('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS valid_ipv6,
    toIPv6OrNull('2001:0db8:85a3::8a2e:0370:7334') AS valid_ipv6_compressed,
    toIPv6OrNull('192.168.0.1') AS ipv4_as_ipv6,
    toIPv6OrNull('not_an_ip') AS invalid_ip;

Result:

| valid_ipv6                    | valid_ipv6_compressed         | ipv4_as_ipv6         | invalid_ip |
|-------------------------------|-------------------------------|----------------------|------------|
| 2001:db8:85a3::8a2e:370:7334  | 2001:db8:85a3::8a2e:370:7334  | ::ffff:192.168.0.1   | NULL       |

This example demonstrates:

Converting a full IPv6 address
Converting a compressed IPv6 address
Converting an IPv4 address to its IPv6 representation
Handling an invalid IP address (returns NULL)

When given a valid IPv4 address, the function returns its IPv6-mapped IPv4 address equivalent.

toIPv6

Converts a string representation of an IPv6 address to the IPv6 data type.

Syntax:

toIPv6(string)

Arguments:

string (String): IP address.

Returns:

The IP address as an IPv6 type.

If the input string is an invalid IPv6 address, it returns an empty value.

If the input string contains a valid IPv4 address, it returns the IPv6 equivalent of that IPv4 address.

Examples:

SELECT
	toIPv6('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS ipv6_address;

Result:

| ipv6_address                 |
|------------------------------|
| 2001:db8:85a3::8a2e:370:7334 |

Example with IPv4 address:

SELECT
	toIPv6('192.0.2.128') AS ipv4_as_ipv6;

Result:

| ipv4_as_ipv6       |
|--------------------|
| ::ffff:192.0.2.128 |

This function is similar to IPv6StringToNum, but returns the IPv6 type instead of a binary format.

If you need to handle invalid input, consider using toIPv6OrDefault or toIPv6OrNull functions.

IPv6StringToNumOrDefault

Converts an IPv6 address from string format to its binary representation. If the input string is not a valid IPv6 address, it returns a default value.

Syntax

IPv6StringToNumOrDefault(ip_address)

Arguments

ip_address (String): IPv6 address as a string.

Returns

Binary representation of the IPv6 address. (FixedString(16))
If the input is not a valid IPv6 address, returns a string of 16 null bytes (equivalent to the IPv6 address ::)

Example

SELECT
  IPv6StringToNumOrDefault('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS ipv6_binary;

Result:

| ipv6_binary                         |
|-------------------------------------|
| 20010db885a3000000008a2e03707334    |

Example with invalid input:

SELECT
  IPv6StringToNumOrDefault('Spicy Taco') AS invalid_ipv6;

Result:

| invalid_ipv6                        |
|-------------------------------------|
| 00000000000000000000000000000000    |

This function is useful when you need to convert IPv6 addresses to their binary form for storage or comparison, and you want to handle invalid inputs gracefully without throwing an exception.

When working with IP addresses, consider using the IPv6 data type for more efficient storage and operations.

IPv6StringToNumOrNull

Converts an IPv6 address from string format to its binary representation. If the input string is not a valid IPv6 address, the function returns NULL.

Syntax

IPv6StringToNumOrNull(ip_address)

Arguments

ip_address (String): IPv6 address as a string.

Returns

The binary representation of the IPv6 address as a FixedString(16).
NULL if the input is not a valid IPv6 address.

Example

SELECT
  IPv6StringToNumOrNull('2001:0db8:85a3:0000:0000:8a2e:0370:7334') AS ipv6_binary;

Result:

| ipv6_binary                             |
|-----------------------------------------|
| 20010db885a3000000008a2e03707334        |

SELECT
  IPv6StringToNumOrNull('2001:0db8:85a3::8a2e:370:7334') AS ipv6_binary;

Result:

| ipv6_binary                             |
|-----------------------------------------|
| 20010db885a3000000008a2e03707334        |

SELECT
  IPv6StringToNumOrNull('Not an IP address') AS invalid_ip;

Result:

| invalid_ip |
|------------|
| NULL       |

This function is useful when you need to convert IPv6 addresses to their binary form for storage or comparison, while safely handling invalid inputs by returning NULL instead of throwing an exception.

isIPv4String

Determines whether the input string is a valid IPv4 address.

Syntax:

isIPv4String(address)

Arguments:

address (String): The IP address to check.

Returns:

1 if the string is a valid IPv4 address, 0 otherwise. [UInt8]

Example:

SELECT
    addr,
    isIPv4String(addr) AS is_ipv4
FROM
(
    SELECT ['192.168.0.1', '2001:0db8:85a3:0000:0000:8a2e:0370:7334', 'not_an_ip', '256.1.2.3', '127.0.0.1'] AS addr
)
ARRAY JOIN addr;

Result:

| addr                                       | is_ipv4 |
|--------------------------------------------|---------|
| 192.168.0.1                                | 1       |
| 2001:0db8:85a3:0000:0000:8a2e:0370:7334    | 0       |
| not_an_ip                                  | 0       |
| 256.1.2.3                                  | 0       |
| 127.0.0.1                                  | 1       |

In this example:

Valid IPv4 addresses return 1.
IPv6 addresses, invalid IP formats, and non-IP strings return 0.
Note that ‘256.1.2.3’ is not a valid IPv4 address as octets cannot exceed 255.

This function returns 0 for IPv6 addresses, even if they are valid. Use isIPv6String for checking IPv6 addresses.

isIPv6String

Determines whether the input string is an IPv6 address or not.

Syntax:

isIPv6String(address)

Arguments:

address (String): The IP address to check.

Returns:

1 if the string is a valid IPv6 address, 0 otherwise. [UInt8]

Example:

SELECT
    addr,
    isIPv6String(addr) AS is_ipv6
FROM
(
    SELECT
        ['::1', '2001:db8::1', '192.168.0.1', 'not_an_ip', '::ffff:192.0.2.128'] AS addr
)
ARRAY JOIN addr;

Result:

| addr                | is_ipv6 |
|---------------------|---------|
| ::1                 | 1       |
| 2001:db8::1         | 1       |
| 192.168.0.1         | 0       |
| not_an_ip           | 0       |
| ::ffff:192.0.2.128  | 1       |

In this example:

::1 and 2001:db8::1 are valid IPv6 addresses, so isIPv6String returns 1.
192.168.0.1 is a valid IPv4 address but not IPv6, so it returns 0.
not_an_ip is not a valid IP address at all, so it returns 0.
::ffff:192.0.2.128 is a valid IPv6 address (IPv4-mapped IPv6 address), so it returns 1.

This function returns 0 for valid IPv4 addresses. Use isIPv4String to check for IPv4 addresses specifically.

isIPAddressInRange

Determines if an IP address is contained within a network specified in CIDR notation.

Syntax:

isIPAddressInRange(address, prefix)

Arguments:

address (String): An IPv4 or IPv6 address.
prefix (String): An IPv4 or IPv6 network prefix in CIDR notation.

Returns:

1 if the IP address is in the specified range, 0 otherwise. (UInt8)

This function supports both IPv4 and IPv6 addresses and networks. It returns 0 if the IP version of the address and the CIDR don’t match.

Examples:

SELECT
  isIPAddressInRange('192.168.0.1', '192.168.0.0/24') AS in_range;

Result:

| in_range |
|----------|
| 1        |

Example with IPv6 address:

SELECT
  isIPAddressInRange('2001:db8::1', '2001:db8::/32') AS in_range;

Result:

| in_range |
|----------|
| 1        |

Example with IP out of range:

SELECT
  isIPAddressInRange('10.0.0.1', '192.168.0.0/16') AS in_range;

Result:

| in_range |
|----------|
| 0        |

Example with IPv6 address and IPv4 CIDR:

SELECT
  isIPAddressInRange('192.168.0.1', '2001:db8::/32') AS in_range;

Result:

| in_range |
|----------|
| 0        |

This function is useful for checking if an IP address belongs to a specific network range, which can be helpful in various networking and security applications.

Introduction

Statements

Functions

IP addresses functions

ClickHouse functions reference

IPv4NumToString

IPv4StringToNum

IPv4StringToNumOrDefault

IPv4StringToNumOrNull

IPv4NumToStringClassC

IPv6NumToString

IPv6StringToNum

IPv6StringToNumOrDefault

IPv6StringToNumOrNull

IPv4ToIPv6

cutIPv6

IPv4CIDRToRange

IPv6CIDRToRange

toIPv4

toIPv4OrDefault

toIPv4OrNull

toIPv6OrDefault

toIPv6OrNull

toIPv6

IPv6StringToNumOrDefault

IPv6StringToNumOrNull

isIPv4String

isIPv6String

isIPAddressInRange

Introduction

Statements

Functions

​ClickHouse functions reference

​IPv4NumToString

​IPv4StringToNum

​IPv4StringToNumOrDefault

​IPv4StringToNumOrNull

​IPv4NumToStringClassC

​IPv6NumToString

​IPv6StringToNum

​IPv6StringToNumOrDefault

​IPv6StringToNumOrNull

​IPv4ToIPv6

​cutIPv6

​IPv4CIDRToRange

​IPv6CIDRToRange

​toIPv4

​toIPv4OrDefault

​toIPv4OrNull

​toIPv6OrDefault

​toIPv6OrNull

​toIPv6

​IPv6StringToNumOrDefault

​IPv6StringToNumOrNull

​isIPv4String

​isIPv6String

​isIPAddressInRange

ClickHouse functions reference

IPv4NumToString

IPv4StringToNum

IPv4StringToNumOrDefault

IPv4StringToNumOrNull

IPv4NumToStringClassC

IPv6NumToString

IPv6StringToNum

IPv6StringToNumOrDefault

IPv6StringToNumOrNull

IPv4ToIPv6

cutIPv6

IPv4CIDRToRange

IPv6CIDRToRange

toIPv4

toIPv4OrDefault

toIPv4OrNull

toIPv6OrDefault

toIPv6OrNull

toIPv6

IPv6StringToNumOrDefault

IPv6StringToNumOrNull

isIPv4String

isIPv6String

isIPAddressInRange