Snippet: Masking IPv6 Address with MySQL

A function to mask an IPv6 address using a stored function with MySQL

// Copyright Robert Eisele 2013
drop function inet6_mask;
DELIMITER |

create function inet6_mask(ip binary(16), mask binary(16))
   returns binary(16)
BEGIN

   DECLARE ret BINARY(16) DEFAULT x'00000000000000000000000000000000';
   DECLARE i INT UNSIGNED DEFAULT 1;

   WHILE i <= 16 DO
      SET ret = CONCAT(substr(ret, 1, i - 1), char(ord(substr(ip, i, 1)) & ord(substr(mask, i, 1))), substr(ret, i + 1));
      SET i = i + 1;
   END WHILE;

   RETURN ret;

END |
delimiter ;


select hex(inet6_mask(x'FDFE0000000000005A55CAFFFEFA9089', x'FFFFFFFFFFFFFFFF000000000000000F'));