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

How do you feel about adding integer columns http://stackoverflow.com/questions/2138706/how-to-check-a-input-ip-fall-in-a-specific-ip-range ? — Fionnuala, May 16 '14 at 12:45
You need to convert the IP addresses into a number (which is what they actually are) then compare them. i.e. 192.168.0.1 = 192*(2^24) + 168 * (2^16) + 0*(2^8) + 1 * (2^0). Find a function that converts an IP address to a number and use that to compare... like this: http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp — Nick.Mc, May 16 '14 at 12:45
@Remou - I would love to however cannot as the database is provided by a third party and changing the database would void any support. — PeanutsMonkey, May 17 '14 at 01:30
Sure you can. You'd need to extract the start and end IP using string functions then convert it to its numeric equivalent. It would be an enormous expression though. What actual constraints do you have with this datbase? You would at least need to create a query to do this. — Nick.Mc, May 17 '14 at 03:43

Gord Thompson · Answer 1 · 2014-05-20T12:43:00.030

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

Hmm, any way i could use the function in Excel as i could interrogate the database using Excel? — PeanutsMonkey, May 17 '14 at 01:33

Yawar · Accepted Answer · 2014-05-17T07:06:27.683

A successful approach would be composed of three parts:

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
```

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

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.

MS Access SQL query for IP address range

2 Answers2