Skip to content

Instantly share code, notes, and snippets.

@0b10011

0b10011/MySQL: Convert dot-delimited IP to long

Created October 3, 2012 15:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save 0b10011/3827693 to your computer and use it in GitHub Desktop.
Save 0b10011/3827693 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
MySQL: Convert dot-delimited IP to long
IF( SUBSTRING_INDEX(`ip`,'.',1)*16777216
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',2),1+LOCATE('.',`ip`))*65536
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX(`ip`,'.',-2)))*256
+ SUBSTRING_INDEX(`ip`,'.',-1)
> 2147483647,
SUBSTRING_INDEX(`ip`,'.',1)*16777216
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',2),1+LOCATE('.',`ip`))*65536
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX(`ip`,'.',-2)))*256
+ SUBSTRING_INDEX(`ip`,'.',-1)
- 4294967296,
SUBSTRING_INDEX(`ip`,'.',1)*16777216
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',2),1+LOCATE('.',`ip`))*65536
+ SUBSTRING(SUBSTRING_INDEX(`ip`,'.',-2),1,-1+LOCATE('.',SUBSTRING_INDEX(`ip`,'.',-2)))*256
+ SUBSTRING_INDEX(`ip`,'.',-1))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment