IP Address conversions

This is the part 2 of converting an IP address to an integer or vice versa. For more details and little introdution see Part 1.

Converting an ip address to integer and back is little straight forward in MySQL compared to SQL Server since SQL Server (2008) doesn’t provide bit shift operators.

In short you can’t do,

SELECT 1<<2 

nor

SELECT 3232261320 -- IP '192.168.100.200'
* (256*256*256*256*256*256) -- Left shift 6 bytes
/ (256*256*256*256*256*256*256) -- Right shift 7 bytes to get 2nd octet

One will encounter “Arithmetic overflow error converting expression to data type int”. Even casting each integer to bigint will not solve the problem as the multiplication results in overflow of signed bigint. And there is no unsigned bigint in SQL Server.

Another way to solve this is to do the following in SQL Server by using bit AND. Retain only the byte that is off interest and then right shift the bits appropriately.

#-- IP '192.168.100.200'
SELECT (cast(3232261320 as bigint) & cast(4278190080 as bigint))/(256*256*256);
SELECT (cast(3232261320 as bigint) & 16711680)/(256*256);
SELECT (cast(3232261320 as bigint) & 65280)/(256);
SELECT (cast(3232261320 as bigint) & 255);

#- And the output will be
192
168
100
200

On systems with unsigned bigint you can also do (MySQL)

SELECT cast(3232261320*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256) 4th_octet;

SELECT cast(3232261320*256*256*256*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256 as unsigned) as 1st_octet;

They will result in outputs 192 and 200 respectively.

With given ip you can simply run the following to get its integer value,

mysql toad>  
SELECT ( ((substring_index(ip, '.',1))<<24)
+ ((substring_index(substring_index(ip, '.', 2), '.', -1))<<16)
+ ((substring_index(substring_index(ip, '.', 3), '.', -1))<<8)
+ ((substring_index(substring_index(ip, '.', 4), '.', -1)<<0))
)
FROM
( SELECT '192.168.100.200' as ip
UNION ALL
SELECT ip FROM som_ip_table;
) t;

Cheers,
Shiva

2 thoughts on “IP Address conversions

  1. Hi,
    First I need to thanks for this post to conversion from IP to number and vice versa. I tested in my environment for IPv6 but I can get correct output.

    Is this approach only for IPv4 address family or both(IPv4 & IPv6).

    If this approach only for Ipv4 then anyone help me how to do this conversion for IPv6.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s