filter varbinary field by length

user_5139911 0
Viewed 23

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
mariadb mysql
1 Answers

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 |
+------------+
Bill_Karwin_208600
Related