This discussion is archived
6 Replies Latest reply: Jan 12, 2013 6:57 AM by sb92075 RSS

How to search IP Address in a Range

user10503319 Newbie
Currently Being Moderated
Hello Experts,

I have come across a functionality where i need to search IP Address in a range using SQL. For example -

Start IP Addres - '10.230.0.1'
End IP Adress - '10.230.0.74'

select * from ip_address_inventory
where ip_address between '10.230.0.1' and '10.230.0.74'

It should result only 74 rows, but it is result all the range till '10.230.0.255' which is not expected

Please help.

Regards,
Vipul Agrawal
  • 1. Re: How to search IP Address in a Range
    Oracle Maniac Explorer
    Currently Being Moderated
    First of all ,welcome to the forum,

    The ip address is not a number (neither its subtype) ,its a varchar2 column i suppose . that is the reason between is giving you unexpected results . The between would be working on the ASCII values .

    you need to convert these ips into number probably by removing the periods ..... user replace or translate and then convert then to number and then use between.For other workarounds ,let the experts suggest .

    Edited by: Rahul K on Jan 12, 2013 1:04 AM
  • 2. Re: How to search IP Address in a Range
    user10503319 Newbie
    Currently Being Moderated
    Thanks Rahul,

    I explored other threads in OTN forum, and now can manage do sorting / searching on IP Address range.

    Created a function as -

    CREATE OR REPLACE function INVDB_CRM.inet_aton( p_str in varchar2 ) return
    number
    as
    l_dot1 number := instr( p_str, '.',1,1 );
    l_dot2 number := instr( p_str, '.',1,2 );
    l_dot3 number := instr( p_str, '.',1,3 );
    begin
    return
    to_number(substr(p_str,1,l_dot1-1))*power(256,3)+
    to_number(substr(p_str,l_dot1+1, l_dot2-l_dot1-1))*power(256,2)+
    to_number(substr(p_str,l_dot2+1,l_dot3-l_dot2-1))*256+
    to_number(substr(p_str,l_dot3+1));
    end;
    /

    and finally the output SQL is -


    SELECT inet_aton (ip_address), ip_address
    FROM IP_ADDRESS_INVENTORY
    WHERE inet_ATON (ip_address) BETWEEN inet_aton ('10.230.5.160')
    AND inet_aton ('10.230.5.171')
    order by (regexp_substr(ip_address, '[^.]+', 1, 1) * 256 * 256 * 256 ) + (regexp_substr(ip_address, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(ip_address, '[^.]+', 1, 3) * 256 )+ regexp_substr(ip_address, '[^.]+', 1, 4)

    I am not sure whether any loop holes in the above approach and it will work as expected.

    Awaiting experts review / reply.

    Regards,
    Vipul
  • 3. Re: How to search IP Address in a Range
    Etbin Guru
    Currently Being Moderated
    Maybe as version unknown
    select *
      from ip_address_inventory
     where ((to_number(substr(ip_address,1,instr(ip_address,'.',1,1) - 1)) * 255 +
             to_number(substr(ip_address,
                              instr(ip_address,'.',1,1) + 1,
                              instr(ip_address,'.',1,2) - instr(ip_address,'.',1,1) - 1
                             )
                      )
            ) * 255 +
             to_number(substr(ip_address,
                              instr(ip_address,'.',1,2) + 1,
                              instr(ip_address,'.',1,3) - instr(ip_address,'.',1,2) - 1
                             )
                      )
           ) * 255 + to_number(substr(ip_address,instr(ip_address,'.',-1,1) + 1))
           between ((10 * 255 + 230) * 255 + 0) * 255 + 1
               and ((10 * 255 + 230) * 255 + 0) * 255 + 74
    Regards

    Etbin

    Edited by: Etbin on 12.1.2013 10:59
    Ooops ! Should have checked before posting

    Edited by: Etbin on 12.1.2013 11:04
    evaluating
    <tt>((p * 255 + q) * 255 + r) * 255 + s</tt><br>
    is more efficient than using
    <tt>p * 255 * 255 * 255 + q * 255 * 255 + r * 255 + s</tt><br>
    ;)
  • 4. Re: How to search IP Address in a Range
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Vipul,

    The basic problem is that ip_address is a string, and the string '255' is sorted before the string '74', because '2' comes before '7'.

    One way to get the results you want is to pad all the numbers in the ip_address to 3 digits; then the string srot order will correspond to the numerical sort order:
    WHERE   REGEXP_REPLACE ( REGEXP_REPLACE ( ip_address
                                     , '(\d+)'
                             , '00\1'
                             )
                     , '0+(\d{3})'
                     , '\1'
                     )  BETWEEN  '010.230.000.001'
                            AND        '010.230.000.074'
    Since this does not call TO_NUMBER, it will not raise a run-time error due to bad data (for example, if one of the ip_addresses has the letter 'O' instead of the digit '0').

    Depending on how this table is used, you might consider adding some redundant columns. You probably want to keep the existing column just the way it is, because that's how people want to enter ip_addresses, and how they want to read them, but you could add another column for the padded address, or maybe even four NUMBER (3) columns for the separate parts. If you're uisng Oracle 11, the redundant columns could be virtual columns, automatically derived from the basic ip_address. In earlier versions, you could use a trigger to automatically populate those columns.
  • 5. Re: How to search IP Address in a Range
    user10503319 Newbie
    Currently Being Moderated
    Thanks everyone, this helped a lot.

    Regards,
    Vipul
  • 6. Re: How to search IP Address in a Range
    sb92075 Guru
    Currently Being Moderated
    IP# are really 32-bit unsigned integers; where each of the "dotted quad" values is just a byte value.
    If you convert the string "WWW.XXX.YYY.ZZZ" to a number the range comparison is trivial.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points