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 throws exception.
Alias: INET_ATON
.
IPv4StringToNumOrDefault(s)β
Same as IPv4StringToNum
, but if the IPv4 address has an invalid format, it returns 0.
IPv4StringToNumOrNull(s)β
Same as IPv4StringToNum
, but if the IPv4 address has an invalid format, it returns null.
IPv4NumToStringClassC(num)β
Similar to IPv4NumToString, but using xxx instead of the last octet.
Example:
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 β
ββββββββββββββββββ΄ββββββββ
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:
SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;
ββaddrββββββββββ
β 2a02:6b8::11 β
ββββββββββββββββ
SELECT
IPv6NumToString(ClientIP6 AS k),
count() AS c
FROM hits_all
WHERE EventDate = today() AND substring(ClientIP6, 1, 12) != unhex('00000000000000000000FFFF')
GROUP BY k
ORDER BY c DESC
LIMIT 10
ββIPv6NumToString(ClientIP6)βββββββββββββββ¬βββββcββ
β 2a02:2168:aaa:bbbb::2 β 24695 β
β 2a02:2698:abcd:abcd:abcd:abcd:8888:5555 β 22408 β
β 2a02:6b8:0:fff::ff β 16389 β
β 2a01:4f8:111:6666::2 β 16016 β
β 2a02:2168:888:222::1 β 15896 β
β 2a01:7e00::ffff:ffff:ffff:222 β 14774 β
β 2a02:8109:eee:ee:eeee:eeee:eeee:eeee β 14443 β
β 2a02:810b:8888:888:8888:8888:8888:8888 β 14345 β
β 2a02:6b8:0:444:4444:4444:4444:4444 β 14279 β
β 2a01:7e00::ffff:ffff:ffff:ffff β 13880 β
βββββββββββββββββββββββββββββββββββββββββββ΄ββββββββ
SELECT
IPv6NumToString(ClientIP6 AS k),
count() AS c
FROM hits_all
WHERE EventDate = today()
GROUP BY k
ORDER BY c DESC
LIMIT 10
ββIPv6NumToString(ClientIP6)ββ¬ββββββcββ
β ::ffff:94.26.111.111 β 747440 β
β ::ffff:37.143.222.4 β 529483 β
β ::ffff:5.166.111.99 β 317707 β
β ::ffff:46.38.11.77 β 263086 β
β ::ffff:79.105.111.111 β 186611 β
β ::ffff:93.92.111.88 β 176773 β
β ::ffff:84.53.111.33 β 158709 β
β ::ffff:217.118.11.22 β 154004 β
β ::ffff:217.118.11.33 β 148449 β
β ::ffff:217.118.11.44 β 148243 β
ββββββββββββββββββββββββββββββ΄βββββββββ
IPv6StringToNumβ
The reverse function of IPv6NumToString. If the IPv6 address has an invalid format, it throws exception.
If the input string contains a valid IPv4 address, returns its IPv6 equivalent. HEX can be uppercase or lowercase.
Alias: INET6_ATON
.
Syntax
IPv6StringToNum(string)
Argument
string
β IP address. String.
Returned value
- IPv6 address in binary format.
Type: FixedString(16).
Example
Query:
SELECT addr, cutIPv6(IPv6StringToNum(addr), 0, 0) FROM (SELECT ['notaddress', '127.0.0.1', '1111::ffff'] AS addr) ARRAY JOIN addr;
Result:
ββaddrββββββββ¬βcutIPv6(IPv6StringToNum(addr), 0, 0)ββ
β notaddress β :: β
β 127.0.0.1 β ::ffff:127.0.0.1 β
β 1111::ffff β 1111::ffff β
ββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββ
See Also
IPv6StringToNumOrDefault(s)β
Same as IPv6StringToNum
, but if the IPv6 address has an invalid format, it returns 0.
IPv6StringToNumOrNull(s)β
Same as IPv6StringToNum
, but if the IPv6 address has an invalid format, it returns null.
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:
SELECT IPv6NumToString(IPv4ToIPv6(IPv4StringToNum('192.168.0.1'))) AS addr;
ββaddrββββββββββββββββ
β ::ffff:192.168.0.1 β
ββββββββββββββββββββββ
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:
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),
cutIPv6(ipv4, 0, 2)
ββcutIPv6(ipv6, 2, 0)ββββββββββββββββββ¬βcutIPv6(ipv4, 0, 2)ββ
β 2001:db8:ac10:fe01:feed:babe:cafe:0 β ::ffff:192.168.0.0 β
βββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββ
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.
SELECT IPv4CIDRToRange(toIPv4('192.168.5.2'), 16);
ββIPv4CIDRToRange(toIPv4('192.168.5.2'), 16)ββ
β ('192.168.0.0','192.168.255.255') β
ββββββββββββββββββββββββββββββββββββββββββββββ
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.
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') β
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
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()
.
WITH
'171.225.130.45' as IPv4_string
SELECT
toTypeName(IPv4StringToNum(IPv4_string)),
toTypeName(toIPv4(IPv4_string))
ββtoTypeName(IPv4StringToNum(IPv4_string))ββ¬βtoTypeName(toIPv4(IPv4_string))ββ
β UInt32 β IPv4 β
ββββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββββ
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 β
βββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββ
toIPv4OrDefault(string)β
Same as toIPv4
, but if the IPv4 address has an invalid format, it returns 0.
toIPv4OrNull(string)β
Same as toIPv4
, but if the IPv4 address has an invalid format, it returns null.
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
toIPv6(string)
Argument
string
β IP address. String
Returned value
- IP address.
Type: IPv6.
Examples
Query:
WITH '2001:438:ffff::407d:1bc1' AS IPv6_string
SELECT
hex(IPv6StringToNum(IPv6_string)),
hex(toIPv6(IPv6_string));
Result:
ββhex(IPv6StringToNum(IPv6_string))ββ¬βhex(toIPv6(IPv6_string))ββββββββββ
β 20010438FFFF000000000000407D1BC1 β 20010438FFFF000000000000407D1BC1 β
βββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββ
Query:
SELECT toIPv6('127.0.0.1');
Result:
ββtoIPv6('127.0.0.1')ββ
β ::ffff:127.0.0.1 β
βββββββββββββββββββββββ
IPv6StringToNumOrDefault(s)β
Same as toIPv6
, but if the IPv6 address has an invalid format, it returns 0.
IPv6StringToNumOrNull(s)β
Same as toIPv6
, but if the IPv6 address has an invalid format, it returns null.
isIPv4Stringβ
Determines whether the input string is an IPv4 address or not. If string
is IPv6 address returns 0
.
Syntax
isIPv4String(string)
Arguments
string
β IP address. String.
Returned value
1
ifstring
is IPv4 address,0
otherwise.
Type: UInt8.
Examples
Query:
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:
ββaddrββββββββββββββ¬βisIPv4String(addr)ββ
β 0.0.0.0 β 1 β
β 127.0.0.1 β 1 β
β ::ffff:127.0.0.1 β 0 β
ββββββββββββββββββββ΄βββββββββββββββββββββ
isIPv6Stringβ
Determines whether the input string is an IPv6 address or not. If string
is IPv4 address returns 0
.
Syntax
isIPv6String(string)
Arguments
string
β IP address. String.
Returned value
1
ifstring
is IPv6 address,0
otherwise.
Type: UInt8.
Examples
Query:
SELECT addr, isIPv6String(addr) FROM ( SELECT ['::', '1111::ffff', '::ffff:127.0.0.1', '127.0.0.1'] AS addr ) ARRAY JOIN addr;
Result:
ββaddrββββββββββββββ¬βisIPv6String(addr)ββ
β :: β 1 β
β 1111::ffff β 1 β
β ::ffff:127.0.0.1 β 1 β
β 127.0.0.1 β 0 β
ββββββββββββββββββββ΄βββββββββββββββββββββ
isIPAddressInRangeβ
Determines if an IP address is contained in a network represented in the CIDR notation. Returns 1
if true, or 0
otherwise.
Syntax
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
or0
.
Type: UInt8.
Example
Query:
SELECT isIPAddressInRange('127.0.0.1', '127.0.0.0/8');
Result:
ββisIPAddressInRange('127.0.0.1', '127.0.0.0/8')ββ
β 1 β
ββββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT isIPAddressInRange('127.0.0.1', 'ffff::/16');
Result:
ββisIPAddressInRange('127.0.0.1', 'ffff::/16')ββ
β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββ
Query:
SELECT isIPAddressInRange('::ffff:192.168.0.1', '::ffff:192.168.0.4/128');
Result:
ββisIPAddressInRange('::ffff:192.168.0.1', '::ffff:192.168.0.4/128')ββ
β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ