Here’s a stored procedure that will let you find an IPv4 address embedded inside an IPv6 address, as a stored procedure in MySQL:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `IPv4_from_IPv6`(IPv6 varchar(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
# Split into blocks
DECLARE Block1, Block2, Block3, Block4, Block5, Block6, Block7, Block8 varchar(4);
DECLARE IPv4 varchar(255);
SELECT SUBSTRING_INDEX(IPv6, ":", 1)Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â INTO Block1;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 2), ":", -1) INTO Block2;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 3), ":", -1) INTO Block3;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 4), ":", -1) INTO Block4;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 5), ":", -1) INTO Block5;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 6), ":", -1) INTO Block6;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 7), ":", -1) INTO Block7;
SELECT SUBSTRING_INDEX(IPv6, ":", -1) INTO Block8;
SELECT INET_NTOA(CONV(concat(Block7, Block8), 16, 10)) INTO IPv4;
IF (Block1 <> 0)
THEN
RETURN NULL;
ELSEIF (Block2 <> 0)
THEN
RETURN NULL;
ELSEIF (Block3 <> 0)
THEN
RETURN NULL;
ELSEIF (Block4 <> 0)
THEN
RETURN NULL;
ELSEIF (Block5 <> 0)
THEN
RETURN NULL;
ELSEIF (Block6 <> "FFFF")
THEN
RETURN NULL;
END IF;
RETURN IPv4;
END $$
Very handy.