MySQL Functions for Converting IP Address to an Integer and Back Again

So after my MySQL server-id idea. I was asked to come up with a function to Convert IP Address. So here it is.

use test;
DROP FUNCTION IF EXISTS IpToInteger;
DELIMITER //
CREATE FUNCTION IpToInteger ( ipAddress CHAR(15) )
RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 char(3);
DECLARE IpInteger INT UNSIGNED;
SET o1 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 1), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 1 – 1) ) + 1), ‘.’, ” );
SET o2 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 2), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 2 – 1) ) + 1), ‘.’, ” );
SET o3 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 3), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 3 – 1) ) + 1), ‘.’, ” );
SET o4 = REPLACE(SUBSTRING(SUBSTRING_INDEX( ipAddress, ‘.’, 4), LENGTH(SUBSTRING_INDEX( ipAddress, ‘.’, 4 – 1) ) + 1), ‘.’, ” );
SELECT ( o1 << 24 ) + ( o2 << 16 ) + ( o3 << 8 ) + o4 INTO IpInteger;
RETURN IpInteger;
END//
DELIMITER ;

use test;
DROP FUNCTION IF EXISTS IntegerToIp;
DELIMITER //
CREATE FUNCTION IntegerToIp( ipInteger INT UNSIGNED )
RETURNS CHAR(16) DETERMINISTIC
BEGIN
DECLARE o1,o2,o3,o4 INT UNSIGNED;
SET o1 = ( ipInteger >> 24 );
SET o2 = ( ipInteger >> 16 << 16 )-( ipInteger >> 24 << 24) >> 16;
SET o3 = (ipInteger >> 8 << 8 ) – ( ipInteger >> 16 << 16 ) >> 8;
SET o4 = ipInteger – ( ipInteger >> 8 << 8 );
RETURN CONCAT( o1, “.” , o2, “.” , o3, “.”, o4 );
END//
DELIMITER ;

mysql> SELECT IpToInteger ( '10.8.208.30' ); +-------------------------------+ | IpToInteger ( '10.8.208.30' ) | +-------------------------------+ | 168349726 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IntegerToIp( 168349726 ); +--------------------------+ | IntegerToIp( 168349726 ) | +--------------------------+ | 10.8.208.30 | +--------------------------+ 1 row in set (0.00 sec) mysql>