CODEWITHSUNDEEP
mysqlmariadb
sanjihan
sanjihan

Reputation: 6014

filter varbinary field by length

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

Upvotes: 2

Views: 62

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

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

Upvotes: 3

Related Questions