filter varbinary field by length



  • I am storing ips using VARBINARY(16) data type. I'd like to select just the ipv4 ips. Is it possible to filter column by the lenght used in VARBINARY (or VARCHAR)? Something like this

    SELECT INET6_NTOA(`ip`) from TABLE where BYTESLENGHT(`ip`) = 4
    


  • If ip_n is the "numeric" version, then

    LENGTH(ip_n)
    

    will be 16 for IPv6 and something smaller for IPv4.

    mysql> SELECT LENGTH(INET_ATON('11.22.33.44')) AS v4,
                  LENGTH(INET6_ATON('f::f')) AS v6;
    +------+------+
    | v4   | v6   |
    +------+------+
    |    9 |   16 |
    +------+------+
    

    If you were starting a string ("A"), this will be true (non-zero) for ip_a (string version) if it is IPv6, or false (zero) for IPv4:

    LOCATE(':', ip_a)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2