decimal_ip_converter.sql (v. 2)

This will let you convert a normal ip address into a decimal ip address

Added: 2006-08-24 06:54:38
Last update: 2010-11-10 16:18:19

download

/******
* You may use and/or modify this script as long as you:
* 1. Keep my name & webpage mentioned
* 2. Don't use it for commercial purposes
*
* If you want to use this script without complying to the rules above, please contact me first at: marty@excudo.net
* 
* Author: Martijn Korse
* Website: http://devshed.excudo.net
*
* Date:  2006-08-24 06:54:38
***/

 /* visitors is an imaginary table that contains information about visitors of a website */
 /* In this table, the ip-field is of the type VARCHAR, and contains the ip of the visitor */
SELECT
	ip,
	(
		(POW(256,3))*
		(substring(ip,1,(LOCATE('.',ip)-1)))
		) +
	(
		(POW(256,2))*
		(substring(ip,(LOCATE('.',ip)+1),(((LOCATE('.',ip,(LOCATE('.',ip)+1))) - (LOCATE('.',ip)) - 1))))
		) +
	(
		(256)*
		(substring(ip,((LOCATE('.',ip,(LOCATE('.',ip)+1)))+1),(((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1))) - (LOCATE('.',ip,(LOCATE('.',ip)+1))) - 1))))
		) +
	(substring(ip,((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1)))+1))) AS decimal_ip
FROM visitors

/**
 * the same functionality, but written as a stored function
 */
CREATE FUNCTION `toDecimalIp`(`ip` VARCHAR(255))
	RETURNS bigint(20)
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
RETURN (
         (POW(256,3))*  
         (substring(ip,1,(LOCATE('.',ip)-1)))  
         ) +  
     (  
         (POW(256,2))*  
         (substring(ip,(LOCATE('.',ip)+1),(((LOCATE('.',ip,(LOCATE('.',ip)+1))) - (LOCATE('.',ip)) - 1))))  
         ) +  
     (  
         (256)*  
         (substring(ip,((LOCATE('.',ip,(LOCATE('.',ip)+1)))+1),(((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1))) - (LOCATE('.',ip,(LOCATE('.',ip)+1))) - 1))))  
         ) +  
     (substring(ip,((LOCATE('.',ip,(LOCATE('.',ip,(LOCATE('.',ip)+1))+1)))+1)))

download