SQL Server IP Compliance Request

Question

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

So, I map as the first part of the input IP address to the entire IP address in the database starting with 5. My request is similar to the 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 displays all IP addresses starting with 5. Now I need to match the second part of the address, which is " 1", from the range of IP addresses in the database. To do this, I will do " order by" again and select the top entry close to the second part of the input IP address.

So, I will do the same for the third and fourth parts. But the question is, how can I do this? I think I need to use a subquery. But where will I 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 The database has ranges of IP addresses, for example: 5.1.82.0up to5.1.82.255

Answer 1

I used the following function for the same thing. Try it and it will work for you too.

It checks if the user's IP address is between the range of IP addresses or not. Below the script will return 1 if the IP address is between the range, otherwise it returns 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

.

Answer 2

IP- ###.###.###.###, ,

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

:

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

Answer 3

try converting the ip address to a large integer to search between range, as shown 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)

Answer 4

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