filter varbinary field by length

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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)

Method 2

There is a IS_IPV4 function as a mechanism too.

So:

SELECT INET6_NTOA(ip)
FROM TABLE
WHERE IS_IPV4(INET6_NTOA(ip))

Note MariaDB-10.5 has a INET6 data type too.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment