ip2long MySQL equivalent

[Son Nguyen]

Hi,

Anyone got an equivalent method of PHP's ip2long() for MySQL (3.23.x)
Thanks

[Son Nguyen]

I created a pretty ugly one! Anyone has some improvement, let me know. Here is it

SELECT 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))";

[r937]

yeah, sql is particularly clumsy when it comes to repetitively scanning a field for a character and pulling out substrings

i mean, it would be rather straightforward if all of the 4 pieces of the IP number were exactly three digits, but they typically aren't

i wrote something similar in microsoft access, it's really ugly too, perhaps even uglier than yours

http://searchdatabase.techtarget.com...285649,00.html

rudy