filter varbinary field by length-CodePudding

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

CodePudding user response：

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 |
 ------------