filter varbinary field by length

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply