查询SQL Server中的匹配IP地址 [英] Query for matching IP address in SQL Server

问题描述

我正在尝试匹配IP地址。输入IP地址可以是 5.1.82.1 。

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

因此，我匹配输入的第一部分包含数据库中以 5 开头的所有IP地址的IP地址。我的查询如下：

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

以上查询会生成以 5 开头的所有IP地址。现在我需要将地址的第二部分 1 与数据库中的IP地址范围进行匹配。为此，我将再次执行订单并选择接近输入IP地址第二部分的顶部记录。

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.

所以我将对第3部分和第4部分做同样的事情。但问题是我该怎么办呢？我想我需要使用子查询。但是我会把它放在我的SQL语句中。


更新：

样本数据

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

重要
在数据库中有IP地址范围，例如： 5.1.82.0 到 5.1.82.255

推荐答案

尝试通过将ip地址转换为大整数来搜索下面的范围，

try by Converting ip address to a big integer to search in between the range like below,

    declare @ips table ([fromip] varchar(15), [toip] varchar(15), country varchar(5))

        insert into @ips values
        ('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')

        declare @ip as varchar(15) ='5.1.82.250'

        SELECT   *
        FROM    @IPs
        WHERE        (Country = 'pk') AND 
        cast(right('00'+PARSENAME(@ip, 4),3)+ right('00'+PARSENAME(@ip, 3),3)+right('00'+PARSENAME(@ip, 2),3) +right('00'+PARSENAME(@ip, 1),3) 
AS bigint) between

        cast(right('00'+PARSENAME(fromip, 4),3)+ right('00'+PARSENAME(fromip, 3),3)+right('00'+PARSENAME(fromip, 2),3) +right('00'+PARSENAME(fromip, 1),3) 
AS bigint) and 
        cast(right('00'+PARSENAME(toip, 4),3)+ right('00'+PARSENAME(toip, 3),3)+right('00'+PARSENAME(toip, 2),3) +right('00'+PARSENAME(toip, 1),3) 
AS bigint)

这篇关于查询SQL Server中的匹配IP地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！