Выберите запись между двумя диапазонами IP

Ответ 1

Больно. SQL Server имеет паршивые функции управления строкой. Однако он предлагает parsename(). Этот подход преобразует IP-адрес в большое десятичное значение для сравнения:

select t.*
from (select t.*,
             (cast(parsename(iplow, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 1) as decimal(12, 0))
             ) as iplow_decimal,
             (cast(parsename(iphigh, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 1) as decimal(12, 0))
             ) as iphigh_decimal
      from t
     ) t
where 192168002050 between iplow_decimal and iphigh_decimal;

Следует отметить, что IP-адреса часто хранятся в базе данных как 4-байтовые целые числа без знака. Это значительно облегчает сравнение. , , хотя вам нужна сложная логика (обычно завернутая в функцию), чтобы преобразовать значения в читаемый формат.

Ответ 2

Попробуйте этот простой способ проверки диапазона

DECLARE @IP NVARCHAR(30)='192.168.500.1'

SELECT  * FROM 
Branches
WHERE
CAST (PARSENAME(@IP,4) AS INT)>=CAST(PARSENAME(IPLow,4) AS INT) AND CAST(PARSENAME(@IP,3) AS INT)>=CAST(PARSENAME(IPLow,3) AS INT) AND CAST(PARSENAME(@IP,2) AS INT)>=CAST(PARSENAME(IPLow,2) AS INT) AND CAST(PARSENAME(@IP,1) AS INT)>=CAST(PARSENAME(IPLow,1) AS INT)
AND
CAST(PARSENAME( @IP,4) AS INT) <= CAST(PARSENAME(IPHigh ,4) AS INT) AND CAST(PARSENAME(@IP ,3) AS INT) <=CAST(PARSENAME(IPHigh ,3) AS INT) AND CAST(PARSENAME(@IP ,2) AS INT) <=CAST(PARSENAME(IPHigh ,2) AS INT) AND CAST(PARSENAME(@IP ,1) AS INT)<=CAST(PARSENAME(IPHigh ,1) AS INT)

AS Per @Ed Haper Комментарий Ролик необходим.

Ответ 3

С помощью этой функции вы можете преобразовать любой IP-адрес в форму, в которой каждая часть имеет 3 цифры. С этим вы можете сделать обычное буквенно-цифровое сравнение. если вы хотите, вы тоже можете вернуть BIGINT...

CREATE FUNCTION dbo.IPWidth3(@IP VARCHAR(100))
RETURNS VARCHAR(15)
BEGIN
DECLARE @RetVal VARCHAR(15);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = STUFF(
        (
        SELECT '.' + REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ),1,1,'') 
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IPWidth3(@IP);

Результат

192.043.002.050

Чтобы отразить здесь комментарий Эд Харпера, эта же функция возвращает DECIMAL(12,0):

CREATE FUNCTION dbo.IP_as_Number(@IP VARCHAR(100))
RETURNS DECIMAL(12,0)
BEGIN
DECLARE @RetVal DECIMAL(12,0);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = 
        CAST((
        SELECT REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ) AS DECIMAL(12,0))
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IP_as_Number(@IP);

Ответ 4

Используйте ниже, чтобы получить ipLow/IPHigh в 4 столбцах. Вы можете использовать эти столбцы для сравнения Ips.

[email protected] VARCHAR(50)='192.168.0.81' 
SELECT (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))

,
substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
''))))),
SUBSTRING((SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))), 0,
PATINDEX('%.%',
(SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))

))),
reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))

Ответ 5

Рассмотрим что-то вроде этого примера, чтобы преобразовать адрес в число.

CREATE FUNCTION dbo.IPAddressAsNumber (@IPAddress AS varchar(15))
RETURNS bigint
BEGIN
RETURN
 CONVERT (bigint,
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 4))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 3))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 2))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 1))) )
END

и с этим вы можете использовать стандартные операторы типа BETWEEN для поиска строк в пределах диапазона, который у вас есть в таблице

DECLARE @t table (ID int, Name varchar(50), Code int, IPLow varchar(15), IPHigh varchar(15))
INSERT INTO @t VALUES 
 (1, 'Lucas', 804645, '192.130.1.1', '192.130.1.254'),
 (2, 'Maria', 222255, '192.168.2.1', '192.168.2.254'),
 (3, 'Julia', 123456, '192.150.3.1', '192.150.3.254')

SELECT * FROM @t
WHERE dbo.IPAddressAsNumber('192.168.2.50')
 BETWEEN dbo.IPAddressAsNumber(IPLow) AND dbo.IPAddressAsNumber(IPHigh)

Схема по существу использует PARSENAME для изоляции каждой части адреса, преобразует каждую часть в двоичную строку SQL, объединяя строки вместе, чтобы получить одну двоичную строку SQL, представляющую адрес, и показывает результат как bigint.

В текстовом представлении шестнадцатеричных значений думайте об этом, разбивая 4 части вместе 192 (0xC0) + 168 (0xA8) + 2 (0x02) + 50 (0x32) в 0xC0A80232. Когда вы включаете эту комбинированную строку в свои двоичные цифры (0 и 1), вы получите то, что можно было бы назвать адресом в бинарной форме, используемой сетевым стеком в таблицах маршрутизации адресов и таблиц маски подсети. Когда вы превращаете это в число в виде целых чисел без знака (или в этом случае bigint), вы получаете 3232236082.

Интересно, что эта схема дает вам "номер", который можно использовать вместо исходного адреса множеством способов. Вы можете, например, пинговать номер 2130706433 вместо адреса 127.0.0.1 - распознаватель имен в Windows будет преобразовывать его аналогично тому, как DNS используется для поиска адреса имени хоста.

Для полноты, вот еще одна функция, которая может быть использована для преобразования формы числа обратно в стандартную строчную форму

CREATE FUNCTION dbo.IPAddressFromNumber (@IPNumber AS bigint)
RETURNS varchar(15)
BEGIN
RETURN
 CONVERT (varchar(15),
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 1,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 2,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 3,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 4,1))) )
END

Ответ 6

select *
from ip a
join ip_details b
on a.ip_address >= b.ip_start
and a.ip_address <= b.ip_end;

При этом таблица "a" содержит список IP-адресов, а таблица "b" содержит диапазоны IP-адресов.

Вместо того, чтобы конвертировать IP-адрес в числовой, мы можем напрямую сравнить строку, он будет делать побайтовое сравнение.

Это работает для меня (PostgreSQL).

Ответ 7

Зависит от того, на какой записи вы ищете максимум или минимум.

select * from table where IPlow like '192.168.2.50' or IPHigh like '192.168.2.50'