MS Access SQL query for IP address range

Question

IP addresses are being stored as text values in an Access database in the format 192.168.0.1 - 192.168.0.254 in a junction table.

Junction table e.g Areas

Name       IPAddress
Area1      192.168.0.1 - 192.168.0.254
Area2      192.168.1.1 - 192.168.1.254

I need to be able to search for records that between these ranges e.g.

SELECT * FROM devices WHERE ipaddress = 192.168.0.1 /Returns record Name1

or

SELECT * FROM tablename WHERE ipaddress BETWEEN 192.168.0.1 AND 192.168.0.25 /Returns record Name1,Name2,Name3,etc

Answer 1

A successful approach would be composed of three parts:

1. Parse the IPAddress column and split it up into two logical (text) columns: IPAddressLow and IPAddressHigh that capture the range of IPs for an area. Let's call this qryAreas:

   select
     [Name]
   , ... as IPAddressLow
   , ... as IPAddressHigh
   from Areas
   

2. Implement a function (in VBA which you can then call from within Access SQL) to do comparisons on IP addresses. The comparator function could be something like:

   ' Returns:
   '  -1 if IP1 < IP2
   '   0 if IP1 = IP2
   '   1 if IP1 > IP2
   Function CompareIPAddresses(ip1 As String, ip2 As String) As Integer
     ip1_arr = Split(ip1, ".")
     ip2_arr = Split(ip2, ".")
   
     For i = 0 To 3
       ip1_arr(i) = CLng(ip1_arr(i))
       ip2_arr(i) = CLng(ip2_arr(i))
     Next i
   
     If ip1 = ip2 Then
       retval = 0
     ElseIf ip1_arr(0) < ip2_arr(0) Then
       retval = -1
     ElseIf ip1_arr(0) = ip2_arr(0) And ip1_arr(1) < ip2_arr(1) Then
       retval = -1
     ElseIf ip1_arr(0) = ip2_arr(0) And ip1_arr(1) = ip2_arr(1) And ip1_arr(2) < ip2_arr(2) Then
       retval = -1
     ElseIf ip1_arr(0) = ip2_arr(0) And ip1_arr(1) = ip2_arr(1) And ip1_arr(2) = ip2_arr(2) And ip1_arr(3) < ip2_arr(3) Then
       retval = -1
     Else
       retval = 1
     End If
   
     CompareIPAddresses = retval
   End Function
   

3. Use the above function in queries to figure out if an IP address is equal to a certain value or falls within a certain range. E.g., if you have an address 192.168.1.100 and want to find out which area it's in, you can do:

   select [Name]
   from qryAreas
   where CompareIPAddresses(IPAddressLow, '192.168.1.100') in (-1, 0)
   and CompareIPAddresses('192.168.1.100', IPAddressHigh) in (-1, 0)
   

The where clause here is the clunkier equivalent of the more elegant where 192.168.1.100 between IPAddressLow and IPAddressHigh syntax, because you don't have a native IP address data type and its corresponding operators--so you're rolling your own.

Answer 2

For searching on a range of IP addresses you might be able to use a little VBA function like this

Option Compare Database
Option Explicit

Public Function ZeroPaddedIP(IP As String) As String
    Dim rtn As String, octets() As String, octet As Variant
    rtn = ""
    octets = Split(IP, ".")
    For Each octet In octets
        rtn = rtn & "." & Format(Val(octet), "000")
    Next
    ZeroPaddedIP = Mid(rtn, 2)  ' trim leading "."
End Function

It pads the octets with leading zeros so

ZeroPaddedIP("192.168.0.1") --> "192.168.000.001"

and your query could do something like

SELECT * FROM tablename 
WHERE ZeroPaddedIP(ipaddress) BETWEEN "192.168.000.001" AND "192.168.000.025"

That query will do a table scan because it cannot use any existing index on [ipaddress]. If performance is an issue, you might consider storing your IP addresses in padded form (either instead of, or in addition to the normal un-padded format).

Edit

For a test table named [NetworkData] ...

ID  IP             Description
--  -------------  -----------
 1  192.168.0.1    router     
 2  192.168.0.2    test server
 3  192.168.0.3    dev server 
 4  192.168.0.102  test client
 5  192.168.0.103  dev client 

... the VBA function shown above could be used in an Access query like this ...

SELECT
    IP,
    ZeroPaddedIP(IP) AS PaddedIP
FROM NetworkData

... to produce the following results ...

IP             PaddedIP
-------------  ---------------
192.168.0.1    192.168.000.001
192.168.0.2    192.168.000.002
192.168.0.3    192.168.000.003
192.168.0.102  192.168.000.102
192.168.0.103  192.168.000.103

... but only if the query is executed from within Access itself. The same results could be obtained from the following query, but this one will work if the query is run against the Access database from some other application (like Excel):

SELECT
    IP,
    Right('000' & Octet1, 3) & '.' & Right('000' & Octet2, 3) & '.' & Right('000' & Octet3, 3) & '.' & Right('000' & Octet4, 3) AS PaddedIP
FROM
    (
        SELECT
            IP,
            Octet1,
            Octet2,
            Left(TheRest2, InStr(TheRest2, '.') - 1) AS Octet3, 
            Mid(TheRest2, InStr(TheRest2, '.') + 1) AS Octet4
        FROM
            (
                SELECT
                    IP,
                    Octet1,
                    Left(TheRest1, InStr(TheRest1, '.') - 1) AS Octet2, 
                    Mid(TheRest1, InStr(TheRest1, '.') + 1) AS theRest2
                FROM
                    (
                        SELECT 
                            IP, 
                            Left(IP, InStr(IP, '.') - 1) AS Octet1, 
                            Mid(IP, InStr(IP, '.') + 1) AS theRest1 
                        FROM NetworkData
                    ) AS q1
            ) AS q2
    ) AS q3

So, if you were querying the data from Excel (or wherever) and you tried to use

SELECT * FROM NetworkData 
WHERE IP Between '192.168.0.1' And '192.168.0.25'

you would get the following incorrect result

ID  IP             Description
--  -------------  -----------
 1  192.168.0.1    router     
 2  192.168.0.2    test server
 4  192.168.0.102  test client
 5  192.168.0.103  dev client 

whereas if you used

SELECT NetworkData.* 
FROM
    NetworkData
    INNER JOIN
    (
        SELECT
            IP,
            Right('000' & Octet1, 3) & '.' & Right('000' & Octet2, 3) & '.' & Right('000' & Octet3, 3) & '.' & Right('000' & Octet4, 3) AS PaddedIP
        FROM
            (
                SELECT
                    IP,
                    Octet1,
                    Octet2,
                    Left(TheRest2, InStr(TheRest2, '.') - 1) AS Octet3, 
                    Mid(TheRest2, InStr(TheRest2, '.') + 1) AS Octet4
                FROM
                    (
                        SELECT
                            IP,
                            Octet1,
                            Left(TheRest1, InStr(TheRest1, '.') - 1) AS Octet2, 
                            Mid(TheRest1, InStr(TheRest1, '.') + 1) AS theRest2
                        FROM
                            (
                                SELECT 
                                    IP, 
                                    Left(IP, InStr(IP, '.') - 1) AS Octet1, 
                                    Mid(IP, InStr(IP, '.') + 1) AS theRest1 
                                FROM NetworkData
                            ) AS q1
                    ) AS q2
            ) AS q3
    ) AS q4
        ON q4.IP = NetworkData.IP
WHERE q4.PaddedIP Between '192.168.000.001' And '192.168.000.025'

you would receive the following correct result

ID  IP           Description
--  -----------  -----------
 1  192.168.0.1  router     
 2  192.168.0.2  test server
 3  192.168.0.3  dev server