Functions for Working with IPv4 and IPv6 Addresses

    IPv4StringToNum(s)

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

    IPv4NumToStringClassC(num)

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

    Example:

    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. └────────────────┴───────┘

    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. 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:6b8:0:fff::ff                      │ 16389 │
    4. │ 2a01:4f8:111:6666::2                    │ 16016 │
    5. │ 2a02:2168:888:222::1                    │ 15896 │
    6. │ 2a01:7e00::ffff:ffff:ffff:222           │ 14774 │
    7. │ 2a02:8109:eee:ee:eeee:eeee:eeee:eeee    │ 14443 │
    8. │ 2a02:810b:8888:888:8888:8888:8888:8888  │ 14345 │
    9. │ 2a02:6b8:0:444:4444:4444:4444:4444      │ 14279 │
    10. │ 2a01:7e00::ffff:ffff:ffff:ffff          │ 13880 │
    11. └─────────────────────────────────────────┴───────┘
    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. └────────────────────────────┴────────┘

    The reverse function of IPv6NumToString. If the IPv6 address has an invalid format, it returns a string of null bytes.
    HEX can be uppercase or lowercase.

    IPv4ToIPv6(x)

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

    1. ┌─addr───────────────┐
    2. │ ::ffff:192.168.0.1 │
    3. └────────────────────┘

    cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4)

    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),
    1. ┌─cutIPv6(ipv6, 2, 0)─────────────────┬─cutIPv6(ipv4, 0, 2)─┐
    2. │ 2001:db8:ac10:fe01:feed:babe:cafe:0 │ ::ffff:192.168.0.0  │
    3. └─────────────────────────────────────┴─────────────────────┘

    Accepts an IPv4 and an UInt8 value containing the . 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)─┐
    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. ┌─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. └───────────────────────────────────┴──────────────────────────┘
    1. WITH
    2.     '2001:438:ffff::407d:1bc1' as IPv6_string
    3. SELECT
    4.     toTypeName(IPv6StringToNum(IPv6_string)),
    5.     toTypeName(toIPv6(IPv6_string))
    1. ┌─toTypeName(IPv6StringToNum(IPv6_string))─┬─toTypeName(toIPv6(IPv6_string))─┐
    2. │ FixedString(16)                          │ IPv6                            │
    3. └──────────────────────────────────────────┴─────────────────────────────────┘
    1. WITH
    2.     '2001:438:ffff::407d:1bc1' as IPv6_string
    3. SELECT
    4.     hex(IPv6StringToNum(IPv6_string)),
    5.     hex(toIPv6(IPv6_string))
    1. ┌─hex(IPv6StringToNum(IPv6_string))─┬─hex(toIPv6(IPv6_string))─────────┐
    2. │ 20010438FFFF000000000000407D1BC1  │ 20010438FFFF000000000000407D1BC1 │