Post reply

Search if IP is in range

  • December 22, 2010 at 9:26 am

    #157366

    I need to create a table of locations and ip ranges. The main aim is to link an host to the place.

    I have 2 problem to solve

    1) given a range (10.131.10.0/24) and a ipaddress (10.169.0.123) how do i check if the given ip is in the range?

    2)how do i order ip addresses? My idea would be to transform ipaddresses to biginteger 10.131.155.23 --> 176397079

    This is how i intend to proceed

    1) create a table

    LOCATIONRANGELOWIPUPIPLOWIP10UPIP10

    London0110.129.0.0/2410.129.0.010.129.0.255176226304176226559

    London0210.130.0.0/2410.130.0.010.130.0.255176291840176292095

    London0310.131.0.0/2410.131.0.010.131.0.255176357376176357631

    2) create an UDF that, given an ip address returns a bigint

    3) location and range come from another application, upon insertion a trigger is fired to complete the row with calculated fields (lowip, lowip10, upip and upip10)

    4) whenever i need to look up in the table, the application connects to the db and run a query like

    select location from table where udf(givenip)>=LOWIP10 and udf(givenip)=<UPIP10

    Ok, i see yor puzzled faces..what do i need?? 😛

    a) what do you think? How would you solve this?

    b) help for the UDF

  • December 22, 2010 at 9:34 am

    #1265590

    I think this may already cover what you are talking about:

    http://www.sqlservercentral.com/articles/SQL+Server/67215/

    CEWII

  • December 22, 2010 at 10:09 am

    #1265607

    BRILLIANT!!

    Thanks a lot 😉

  • December 22, 2010 at 10:20 am

    #1265609

    You are very welcome.. No need to re-invent the wheel..

    CEWII

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply