none
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

Answers

  • 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 K.Kalyan 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
    Moderator
  • 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 K.Kalyan 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