# Convert IP Address to Binary

### Question

• Hi, I have a 2 fields where I have the IPAddress's in them as values.  I have taken one random value '191.121.155.1'. Now I need to find the range of this IP address is falling in those fields.

Example:
IP-1                                        IP-2
191.121.153.1                         191.121.155.2

So now I have to find is the IPAddress  '191.121.155.1'  falling in the IP-1 and IP-2. For that I'm thinking to convert them into binary and find whether its falling in that range or not.

Now how do i convert the IP address to binary?

Wednesday, May 04, 2011 7:20 PM

• One way is to use ParseName function, for example

```Declare @Test Table(IP1 varchar(15), IP2 varchar(15));
Insert @Test (IP1, IP2) Values
('191.121.153.1', '191.121.155.2'),
('180.1.12.13', '200.255.255.255'),
('10.0.0.0', '10.255.255.255');
Declare @IPtoTest varchar(15);
Set @IPtoTest = '191.121.155.1'

Select *, Case When P3 Between P1 And P2 Then 'Yes' Else 'No' End As InRange
From @Test
Cross Apply (Select Cast(ParseName(IP1, 4) As bigint) * 256*256*256 +
Cast(ParseName(IP1, 3) As bigint) * 256*256 +
ParseName(IP1, 2) * 256 +
ParseName(IP1, 1) As P1) As x
Cross Apply (Select Cast(ParseName(IP2, 4) As bigint) * 256*256*256 +
Cast(ParseName(IP2, 3) As bigint) * 256*256 +
ParseName(IP2, 2) * 256 +
ParseName(IP2, 1) As P2) As y
Cross Join (Select Cast(ParseName(@IPtoTest, 4) As bigint) * 256*256*256 +
Cast(ParseName(@IPtoTest, 3) As bigint) * 256*256 +
ParseName(@IPtoTest, 2) * 256 +
ParseName(@IPtoTest, 1) As P3) As z;
```

Tom

• Marked as answer by Wednesday, May 04, 2011 8:35 PM
Wednesday, May 04, 2011 7:37 PM

### All replies

• First, give a look at the PARSENAME function in books online -- for example:

```declare @test table (ip varchar(15))
insert into @test
select '191.121.153.1' union all select '192.168.2.1'
--select * from @test

select
ip,
seg1,
seg2,
seg3,
seg4
from @test
cross apply
( select
cast(parsename(ip,4) as tinyint) as seg1,
cast(parsename(ip,3) as tinyint) as seg2,
cast(parsename(ip,2) as tinyint) as seg3,
cast(parsename(ip,1) as tinyint) as seg4
) x
/* -------- Output: --------
ip       seg1 seg2 seg3 seg4
--------------- ---- ---- ---- ----
191.121.153.1  191 121 153 1
192.168.2.1   192 168 2  1

(2 row(s) affected)
*/
```

.

Wednesday, May 04, 2011 7:36 PM
• One way is to use ParseName function, for example

```Declare @Test Table(IP1 varchar(15), IP2 varchar(15));
Insert @Test (IP1, IP2) Values
('191.121.153.1', '191.121.155.2'),
('180.1.12.13', '200.255.255.255'),
('10.0.0.0', '10.255.255.255');
Declare @IPtoTest varchar(15);
Set @IPtoTest = '191.121.155.1'

Select *, Case When P3 Between P1 And P2 Then 'Yes' Else 'No' End As InRange
From @Test
Cross Apply (Select Cast(ParseName(IP1, 4) As bigint) * 256*256*256 +
Cast(ParseName(IP1, 3) As bigint) * 256*256 +
ParseName(IP1, 2) * 256 +
ParseName(IP1, 1) As P1) As x
Cross Apply (Select Cast(ParseName(IP2, 4) As bigint) * 256*256*256 +
Cast(ParseName(IP2, 3) As bigint) * 256*256 +
ParseName(IP2, 2) * 256 +
ParseName(IP2, 1) As P2) As y
Cross Join (Select Cast(ParseName(@IPtoTest, 4) As bigint) * 256*256*256 +
Cast(ParseName(@IPtoTest, 3) As bigint) * 256*256 +
ParseName(@IPtoTest, 2) * 256 +
ParseName(@IPtoTest, 1) As P3) As z;
```

Tom

• Marked as answer by Wednesday, May 04, 2011 8:35 PM
Wednesday, May 04, 2011 7:37 PM
• Thanks Tom, I got want I need from this query.
Wednesday, May 04, 2011 8:53 PM
• hey Tom, can you let me know why did we multiplied by 256
Wednesday, May 04, 2011 10:43 PM
• Because each of the parts in an IP address uses 8 bits.  And 2 to the power of 8 is 256 (that's why the allowed values in each part are 0-255).  So an IP address like 10.56.100.255 would be in binary

```00000110 00111000 01100100 11111111
which is
10      56       100     255
if you were working in base 256.```

So just like 357 in base 10 is 3 times 10 squared + 5 times 10 + 7, in base 256 10.56.100.255 is 10*256 cubed + 56 * 256 squared + 100*256 + 255.

Tom

Wednesday, May 04, 2011 11:02 PM