samedi 25 avril 2015

Query for matching IP address in SQL Server

I am trying to match IP addresses. The input IP address can be "5.1.82.1".

So, I am matching like first part of input IP address with all the IP address in database that start with 5. My query is like following

SELECT     top 1   PARSENAME(ipaddress, 4) AS firstpart, ipaddress
FROM            IPs
WHERE        (Country = 'pk') AND (PARSENAME(ipaddress, 4) <= '5')
ORDER BY Expr2 DESC

The above query results all the IP addresses that start with 5. Now I need to match the second part of the address which is "1" against the IP address range in the database. For that I will again do an "order by" and select the top record that is close to second part of the input IP address.

And so I will do the same for 3rd part and 4th part. But the question is how I can do it? I think I need to use a subquery. But where I will put it in my SQL statement.
UPDATE:
Sample data

from              to                  country
[5.1.82.0]       [5.1.82.255]         PK
[5.39.250.0]     [5.39.253.255]       PK
[5.39.255.0]     [5.39.255.255]       PK

IMPORTANT In database there are ranges of IP addresses for example: 5.1.82.0 to 5.1.82.255

Aucun commentaire:

Enregistrer un commentaire