Functions for Working with IPv4 and IPv6 Addresses

IPv4NumToString(num)

Takes a UInt32 number. Interprets it as an IPv4 address in big endian. Returns a string containing the corresponding IPv4 address in the format A.B.C.d (dot-separated numbers in decimal form).

Alias: INET_NTOA.

IPv4StringToNum(s)

The reverse function of IPv4NumToString. If the IPv4 address has an invalid format, it returns 0.

Alias: INET_ATON.

IPv4NumToStringClassC(num)

Similar to IPv4NumToString, but using xxx instead of the last octet.

Example:

  1. SELECT
  2. IPv4NumToStringClassC(ClientIP) AS k,
  3. count() AS c
  4. FROM test.hits
  5. GROUP BY k
  6. ORDER BY c DESC
  7. LIMIT 10
  1. ┌─k──────────────┬─────c─┐
  2. 83.149.9.xxx 26238
  3. 217.118.81.xxx 26074
  4. 213.87.129.xxx 25481
  5. 83.149.8.xxx 24984
  6. 217.118.83.xxx 22797
  7. 78.25.120.xxx 22354
  8. 213.87.131.xxx 21285
  9. 78.25.121.xxx 20887
  10. 188.162.65.xxx 19694
  11. 83.149.48.xxx 17406
  12. └────────────────┴───────┘

Since using ‘xxx’ is highly unusual, this may be changed in the future. We recommend that you do not rely on the exact format of this fragment.

IPv6NumToString(x)

Accepts a FixedString(16) value containing the IPv6 address in binary format. Returns a string containing this address in text format.
IPv6-mapped IPv4 addresses are output in the format ::ffff:111.222.33.44.

Alias: INET6_NTOA.

Examples:

  1. SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;
  1. ┌─addr─────────┐
  2. 2a02:6b8::11
  3. └──────────────┘
  1. SELECT
  2. IPv6NumToString(ClientIP6 AS k),
  3. count() AS c
  4. FROM hits_all
  5. WHERE EventDate = today() AND substring(ClientIP6, 1, 12) != unhex('00000000000000000000FFFF')
  6. GROUP BY k
  7. ORDER BY c DESC
  8. LIMIT 10
  1. ┌─IPv6NumToString(ClientIP6)──────────────┬─────c─┐
  2. 2a02:2168:aaa:bbbb::2 24695
  3. 2a02:2698:abcd:abcd:abcd:abcd:8888:5555 22408
  4. 2a02:6b8:0:fff::ff 16389
  5. 2a01:4f8:111:6666::2 16016
  6. 2a02:2168:888:222::1 15896
  7. 2a01:7e00::ffff:ffff:ffff:222 14774
  8. 2a02:8109:eee:ee:eeee:eeee:eeee:eeee 14443
  9. 2a02:810b:8888:888:8888:8888:8888:8888 14345
  10. 2a02:6b8:0:444:4444:4444:4444:4444 14279
  11. 2a01:7e00::ffff:ffff:ffff:ffff 13880
  12. └─────────────────────────────────────────┴───────┘
  1. SELECT
  2. IPv6NumToString(ClientIP6 AS k),
  3. count() AS c
  4. FROM hits_all
  5. WHERE EventDate = today()
  6. GROUP BY k
  7. ORDER BY c DESC
  8. LIMIT 10
  1. ┌─IPv6NumToString(ClientIP6)─┬──────c─┐
  2. ::ffff:94.26.111.111 747440
  3. ::ffff:37.143.222.4 529483
  4. ::ffff:5.166.111.99 317707
  5. ::ffff:46.38.11.77 263086
  6. ::ffff:79.105.111.111 186611
  7. ::ffff:93.92.111.88 176773
  8. ::ffff:84.53.111.33 158709
  9. ::ffff:217.118.11.22 154004
  10. ::ffff:217.118.11.33 148449
  11. ::ffff:217.118.11.44 148243
  12. └────────────────────────────┴────────┘

IPv6StringToNum

The reverse function of IPv6NumToString. If the IPv6 address has an invalid format, it returns a string of null bytes.

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

Alias: INET6_ATON.

Syntax

  1. IPv6StringToNum(string)

Argument

  • string — IP address. String.

Returned value

  • IPv6 address in binary format.

Type: FixedString(16).

Example

Query:

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

Result:

  1. ┌─addr───────┬─cutIPv6(IPv6StringToNum(addr), 0, 0)─┐
  2. notaddress ::
  3. 127.0.0.1 ::ffff:127.0.0.1
  4. 1111::ffff 1111::ffff
  5. └────────────┴──────────────────────────────────────┘

See Also

IPv4ToIPv6(x)

Takes a UInt32 number. Interprets it as an IPv4 address in big endian. Returns a FixedString(16) value containing the IPv6 address in binary format. Examples:

  1. SELECT IPv6NumToString(IPv4ToIPv6(IPv4StringToNum('192.168.0.1'))) AS addr;
  1. ┌─addr───────────────┐
  2. ::ffff:192.168.0.1
  3. └────────────────────┘

cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4)

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. For example:

  1. WITH
  2. IPv6StringToNum('2001:0DB8:AC10:FE01:FEED:BABE:CAFE:F00D') AS ipv6,
  3. IPv4ToIPv6(IPv4StringToNum('192.168.0.1')) AS ipv4
  4. SELECT
  5. cutIPv6(ipv6, 2, 0),
  6. cutIPv6(ipv4, 0, 2)
  1. ┌─cutIPv6(ipv6, 2, 0)─────────────────┬─cutIPv6(ipv4, 0, 2)─┐
  2. 2001:db8:ac10:fe01:feed:babe:cafe:0 ::ffff:192.168.0.0
  3. └─────────────────────────────────────┴─────────────────────┘

IPv4CIDRToRange(ipv4, Cidr),

Accepts an IPv4 and an UInt8 value containing the CIDR. Return a tuple with two IPv4 containing the lower range and the higher range of the subnet.

  1. SELECT IPv4CIDRToRange(toIPv4('192.168.5.2'), 16);
  1. ┌─IPv4CIDRToRange(toIPv4('192.168.5.2'), 16)─┐
  2. ('192.168.0.0','192.168.255.255')
  3. └────────────────────────────────────────────┘

IPv6CIDRToRange(ipv6, Cidr),

Accepts an IPv6 and an UInt8 value containing the CIDR. Return a tuple with two IPv6 containing the lower range and the higher range of the subnet.

  1. SELECT IPv6CIDRToRange(toIPv6('2001:0db8:0000:85a3:0000:0000:ac1f:8001'), 32);
  1. ┌─IPv6CIDRToRange(toIPv6('2001:0db8:0000:85a3:0000:0000:ac1f:8001'), 32)─┐
  2. ('2001:db8::','2001:db8:ffff:ffff:ffff:ffff:ffff:ffff')
  3. └────────────────────────────────────────────────────────────────────────┘

toIPv4(string)

An alias to IPv4StringToNum() that takes a string form of IPv4 address and returns value of IPv4 type, which is binary equal to value returned by IPv4StringToNum().

  1. WITH
  2. '171.225.130.45' as IPv4_string
  3. SELECT
  4. toTypeName(IPv4StringToNum(IPv4_string)),
  5. toTypeName(toIPv4(IPv4_string))
  1. ┌─toTypeName(IPv4StringToNum(IPv4_string))─┬─toTypeName(toIPv4(IPv4_string))─┐
  2. UInt32 IPv4
  3. └──────────────────────────────────────────┴─────────────────────────────────┘
  1. WITH
  2. '171.225.130.45' as IPv4_string
  3. SELECT
  4. hex(IPv4StringToNum(IPv4_string)),
  5. hex(toIPv4(IPv4_string))
  1. ┌─hex(IPv4StringToNum(IPv4_string))─┬─hex(toIPv4(IPv4_string))─┐
  2. ABE1822D ABE1822D
  3. └───────────────────────────────────┴──────────────────────────┘

toIPv6

Converts a string form of IPv6 address to IPv6 type. If the IPv6 address has an invalid format, returns an empty value.
Similar to IPv6StringToNum function, which converts IPv6 address to binary format.

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

Syntax

  1. toIPv6(string)

Argument

  • string — IP address. String

Returned value

  • IP address.

Type: IPv6.

Examples

Query:

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

Result:

  1. ┌─hex(IPv6StringToNum(IPv6_string))─┬─hex(toIPv6(IPv6_string))─────────┐
  2. 20010438FFFF000000000000407D1BC1 20010438FFFF000000000000407D1BC1
  3. └───────────────────────────────────┴──────────────────────────────────┘

Query:

  1. SELECT toIPv6('127.0.0.1');

Result:

  1. ┌─toIPv6('127.0.0.1')─┐
  2. ::ffff:127.0.0.1
  3. └─────────────────────┘

isIPv4String

Determines whether the input string is an IPv4 address or not. If string is IPv6 address returns 0.

Syntax

  1. isIPv4String(string)

Arguments

  • string — IP address. String.

Returned value

  • 1 if string is IPv4 address, 0 otherwise.

Type: UInt8.

Examples

Query:

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

Result:

  1. ┌─addr─────────────┬─isIPv4String(addr)─┐
  2. 0.0.0.0 1
  3. 127.0.0.1 1
  4. ::ffff:127.0.0.1 0
  5. └──────────────────┴────────────────────┘

isIPv6String

Determines whether the input string is an IPv6 address or not. If string is IPv4 address returns 0.

Syntax

  1. isIPv6String(string)

Arguments

  • string — IP address. String.

Returned value

  • 1 if string is IPv6 address, 0 otherwise.

Type: UInt8.

Examples

Query:

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

Result:

  1. ┌─addr─────────────┬─isIPv6String(addr)─┐
  2. :: 1
  3. 1111::ffff 1
  4. ::ffff:127.0.0.1 1
  5. 127.0.0.1 0
  6. └──────────────────┴────────────────────┘

isIPAddressInRange

Determines if an IP address is contained in a network represented in the CIDR notation. Returns 1 if true, or 0 otherwise.

Syntax

  1. isIPAddressInRange(address, prefix)

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.

Arguments

  • address — An IPv4 or IPv6 address. String.
  • prefix — An IPv4 or IPv6 network prefix in CIDR. String.

Returned value

  • 1 or 0.

Type: UInt8.

Example

Query:

  1. SELECT isIPAddressInRange('127.0.0.1', '127.0.0.0/8');

Result:

  1. ┌─isIPAddressInRange('127.0.0.1', '127.0.0.0/8')─┐
  2. 1
  3. └────────────────────────────────────────────────┘

Query:

  1. SELECT isIPAddressInRange('127.0.0.1', 'ffff::/16');

Result:

  1. ┌─isIPAddressInRange('127.0.0.1', 'ffff::/16')─┐
  2. 0
  3. └──────────────────────────────────────────────┘