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

回答1:

I used following function for same thing.Try it and it should work for you also.

It verifies whether the IP Address of the user is between the range of IP Addresses or not. Below script will return 1, If IP Address is between the range, otherwise it return 0;

CREATE FUNCTION IsIPAddressInRange
(
    @IPAddress varchar(20),
    @StartRange varchar(20),
    @EndRange varchar(20)
)
RETURNS INT
AS
BEGIN
    DECLARE @MAXRANGE BIGINT = 256
    RETURN 
    CASE 
    WHEN PARSENAME(@IPAddress,1) + @MAXRANGE * PARSENAME(@IPAddress,2) + 
    @MAXRANGE * @MAXRANGE * PARSENAME(@IPAddress ,3) + @MAXRANGE * @MAXRANGE * @MAXRANGE * PARSENAME(@IPAddress ,4)
    BETWEEN
    PARSENAME(@StartRange,1) + @MAXRANGE * PARSENAME(@StartRange,2) + 
    @MAXRANGE * @MAXRANGE * PARSENAME(@StartRange ,3) + @MAXRANGE * @MAXRANGE * @MAXRANGE * PARSENAME(@StartRange ,4)
    AND
    PARSENAME(@EndRange,1) + @MAXRANGE * PARSENAME(@EndRange,2) + 
    @MAXRANGE * @MAXRANGE * PARSENAME(@EndRange ,3) + @MAXRANGE * @MAXRANGE * @MAXRANGE * PARSENAME(@EndRange ,4)
    THEN 1
    ELSE 0
    END     
END

I took above function from HERE.

回答2:

You can make a function to have all IPs in a formatted type of ###.###.###.###, then you can use it to any check you want

CREATE FUNCTION dbo.formatIP ( @ip varchar(20) )
RETURNS varchar(20)
AS
BEGIN
    RETURN RIGHT('000'+PARSENAME(@ip,4), 3) + '.' + RIGHT('000'+PARSENAME(@ip,3), 3) + '.' + RIGHT('000'+PARSENAME(@ip,2), 3) + '.' + RIGHT('000'+PARSENAME(@ip,1), 3)
END
GO

Now you can use it like this:

DECLARE @ip1 varchar(20) = '5.1.82.205', @from varchar(20) = '5.1.82.0', @to varchar(20) = '5.1.82.255'

SELECT 
    CASE 
        WHEN dbo.formatIP(@ip1) BETWEEN dbo.formatIP(@from) AND dbo.formatIP(@to) THEN 1 
        ELSE 0 
    END

回答3:

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)

回答4:

    <?php
    $st = '5.1.82.1';
    echo strstr($st, '.1.82.1',true)
    ?>