filter varbinary field by length

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

Answer 1

I'd use LENGTH(), which returns the length of a string in bytes. It supports both VARCHAR or VARBINARY.

mysql> create table mytable (ip varbinary(16));

mysql> insert into mytable set ip = inet6_aton('192.168.1.1');

mysql> insert into mytable set ip = inet6_aton('fdfe::5a55:caff:fefa:9089');

mysql> select length(ip) from mytable;
+------------+
| length(ip) |
+------------+
|          4 |
|         16 |
+------------+