IP address within range query

Posted on 2005-04-19
Last Modified: 2008-03-03
I want to use joins or where clauses to see whether an IP address matches within a range.

This is for mapping locations to ip addresses

For example

Table: LocIP
LocationName      IPRange
Wales                 192.168.1.%

Table: PC
ComputerName          IPAddress        LocationName
COMP1               NULL
COMP2               NULL
COMP3               NULL

The % rule is easy enough.

update pc
set LocationName = LocIP.LocationName
from pc inner join LocIP on pc.IPAddress LIKE LocIP.Range

Any ideas on how to match the more complex rules using sql?

Question by:plq
    LVL 28

    Expert Comment

    If your IP Range is composed of 2 fields, namely StartRange and EndRange, this will be easier.  And your IPRange is just the prefix (for example, 192.168.1).  If this is the case, you can use this query:

    SET LocationName = LocIP.LocationName
    ON LEFT(PC.IPAddress, LEN(LocIP.IPPrefix)) = LocIP.IPPrefix AND
         RIGHT(PC.IPAddress, CHARINDEX('.', REVERSE(PC.IPAddress))) BETWEEN LocIP.StartRange AND LocIP.EndRange
    LVL 68

    Assisted Solution

    Are the "variables" only in the last node?  

    If so, I suggest using from and to values for that node rather than a single range:

    LocationName      IPBase   From  To
    Wales                 192.168.1   0      255
    England               192.168.2   1      64
    Scotland              192.168.2   65    96
    Ireland                192.168.2   96    254

    You can use computed columns to get the IPFrom and IPTo:

    on pc.IPAddress LIKE BETWEEN IPFrom AND IPTo
    LVL 5

    Expert Comment

    For better solutions:
    Please see an article provided in this month's SQL server magazine, regarding handling IP address.

    You can save/sort/join the IP addresses as 4 tinyints, 1 bigint, and other cool methods. Please refer to the paper for detailed information.

    Personall recommendation - don't store your IP addresses as strings. Strings are only needed for the presentation of addresses. For all calculation matter, store the IP as bigint or a combo of tinyints.

    Hope this helps.

    LVL 68

    Expert Comment


    ADD IPFrom AS IPBase + '.' + From,
        IPTo AS IPBase + '.' + To
    LVL 8

    Author Comment

    I could have the ip range as two fields. Thats a new table so I can control the db design. I can live with part III of the IP address being a single value, so users would have to enter a record for each 255 range - thats ok

    The IP address on the PC record must be stored as a single string, that cannot be changed

    The problem with a "between" is that the IPs are text, so if you;ve got thats going to include isn't it

    Perhaps with scotts model:

    ON pc.IPAddress LIKE IPBase + '%' AND SubString(replace(pc.IPAddress, IPBase, ''), 2, 255) BETWEEN IPFrom and IPTo  

    LVL 75

    Accepted Solution

    I suggest you use the PARSENAME() T-SQL function to extract the individual parts out. That way you are not killing yourself using CHARINDEX and other cumbersome string functions.  For example,

    Declare @IPAddress char(15)
    Set @IPAddress = ''
    Select PARSENAME(@IPAddress, 4), PARSENAME(@IPAddress, 3), PARSENAME(@IPAddress, 2), PARSENAME(@IPAddress, 1)

    192      168      2      70  

    You can then use CAST to tinyint to get the right range.
    LVL 8

    Author Comment

    Cool - didn't know about parsename. Researching...
    LVL 68

    Expert Comment

    >> The problem with a "between" is that the IPs are text <<

    D'OH, quite right, I should have thought of that
    LVL 8

    Author Comment

    Well you're not half as daft as I am, otherwise I'd be answering not asking. :)))

    Just coming up with a solution now using these ideas,... back soon


    LVL 8

    Author Comment

    Yer tiz...

    select a.PCID, a.IP , b.TargetValue1
    from pc as a
    inner join maptable as b on b.MappingType = 'iptolocation'
    and parsename(a.IP, 4) = parsename(b.SourceValue1, 4)
    and parsename(a.IP, 3) = parsename(b.SourceValue1, 3)
    and parsename(a.IP, 2) = parsename(b.SourceValue1, 2)
    and convert(int,parsename(a.IP, 1)) between convert(int, parsename(b.SourceValue1, 1)) AND convert(int, parsename(b.SourceValue2, 1))
    where specificationid = 258

    This just works on a 255 subnet, but is easily extended to the other parts of the address

    Got fooled by parsename working backwards, element 1 is the rightmost, but this seems to work ok

    thanks for helping. Points coming up
    LVL 75

    Expert Comment

    by:Anthony Perkins
    You don't need to convert to int, CASTing to tinyint is enough to get the range.

    Thanks for the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now