6 Replies Latest reply: Jan 12, 2013 8:57 AM by sb92075 RSS

    How to search IP Address in a Range

    user10503319
      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
          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
            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
              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
                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
                  Thanks everyone, this helped a lot.

                  Regards,
                  Vipul
                  • 6. Re: How to search IP Address in a Range
                    sb92075
                    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.