CREATE FUNCTION dbo.fnIPtoBigInt
(
@Ipaddress NVARCHAR(15) -- should be in the form '123.123.123.123'
)
RETURNS BIGINT
AS
BEGIN
DECLARE @part1 AS NVARCHAR(3)
DECLARE @part2 AS NVARCHAR(3)
DECLARE @part3 AS NVARCHAR(3)
DECLARE @part4 AS NVARCHAR(3)
SELECT @part1 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
SELECT @Ipaddress = SUBSTRING(@Ipaddress, LEN(@part1) + 2, 15)
SELECT @part2 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
SELECT @Ipaddress = SUBSTRING(@Ipaddress, LEN(@part2) + 2, 15)
SELECT @part3 = LEFT(@Ipaddress, CHARINDEX('.',@Ipaddress) - 1)
SELECT @part4 = SUBSTRING(@Ipaddress, LEN(@part3) + 2, 15)
DECLARE @ipAsBigInt AS BIGINT
SELECT @ipAsBigInt =
(16777216 * (CAST(@part1 AS BIGINT)))
+ (65536 * (CAST(@part2 AS BIGINT)))
+ (256 * (CAST(@part3 AS BIGINT)))
+ (CAST(@part4 AS BIGINT))
RETURN @ipAsBigInt
END
GO
然后您可以轻松地实现一个函数来测试地址是否在子网中:
CREATE FUNCTION dbo.fnIsIpaddressInSubnet
(
@networkAddress NVARCHAR(15), -- 'eg: '192.168.0.0'
@subnetMask NVARCHAR(15), -- 'eg: '255.255.255.0' for '/24'
@testAddress NVARCHAR(15) -- 'eg: '192.168.0.1'
)
RETURNS BIT AS
BEGIN
RETURN CASE WHEN (dbo.fnIPtoBigInt(@networkAddress) & dbo.fnIPtoBigInt(@subnetMask))
= (dbo.fnIPtoBigInt(@testAddress) & dbo.fnIPtoBigInt(@subnetMask))
THEN 1 ELSE 0 END
END
CREATE FUNCTION dbo.fnSubnetBitstoBigInt
(
@SubnetBits TINYINT -- max = 32
)
RETURNS BIGINT
AS
BEGIN
DECLARE @multiplier AS BIGINT = 2147483648
DECLARE @ipAsBigInt AS BIGINT = 0
DECLARE @bitIndex TINYINT = 1
WHILE @bitIndex <= @SubnetBits
BEGIN
SELECT @ipAsBigInt = @ipAsBigInt + @multiplier
SELECT @multiplier = @multiplier / 2
SELECT @bitIndex = @bitIndex + 1
END
RETURN @ipAsBigInt
END
GO
CREATE FUNCTION dbo.fnIsIpaddressInSubnetShortHand
(
@network NVARCHAR(18), -- 'eg: '192.168.0.0/24'
@testAddress NVARCHAR(15) -- 'eg: '192.168.0.1'
)
RETURNS BIT AS
BEGIN
DECLARE @networkAddress NVARCHAR(15)
DECLARE @subnetBits TINYINT
SELECT @networkAddress = LEFT(@network, CHARINDEX('/', @network) - 1)
SELECT @subnetBits = CAST(SUBSTRING(@network, LEN(@networkAddress) + 2, 2) AS TINYINT)
RETURN CASE WHEN (dbo.fnIPtoBigInt(@networkAddress) & dbo.fnSubnetBitstoBigInt(@subnetBits))
= (dbo.fnIPtoBigInt(@testAddress) & dbo.fnSubnetBitstoBigInt(@subnetBits))
THEN 1 ELSE 0 END
END
SELECT dbo.fnIsIpaddressInSubnetShorthand('192.168.2.0/24','192.168.3.91') -- returns 0
SELECT dbo.fnIsIpaddressInSubnetShorthand('192.168.2.0/24','192.168.2.91') -- returns 1
完整解决方案
CREATE
OR ALTER FUNCTION dbo.IPv4SubnetContainsIPAddress (
@net AS VARCHAR(15),
@mask AS VARCHAR(15),
@ip AS VARCHAR(15)
) RETURNS tinyint AS BEGIN DECLARE @result AS tinyint IF LEN(@mask) <= 2
SELECT
@mask = m
FROM
(
VALUES
(0, '0.0.0.0'),
(1, '128.0.0.0'),
(2, '192.0.0.0'),
(3, '224.0.0.0'),
(4, '240.0.0.0'),
(5, '248.0.0.0'),
(6, '252.0.0.0'),
(7, '254.0.0.0'),
(8, '255.0.0.0'),
(9, '255.128.0.0'),
(10, '255.192.0.0'),
(11, '255.224.0.0'),
(12, '255.240.0.0'),
(13, '255.248.0.0'),
(14, '255.252.0.0'),
(15, '255.254.0.0'),
(16, '255.255.0.0'),
(17, '255.255.128.0'),
(18, '255.255.192.0'),
(19, '255.255.224.0'),
(20, '255.255.240.0'),
(21, '255.255.248.0'),
(22, '255.255.252.0'),
(23, '255.255.254.0'),
(24, '255.255.255.0'),
(25, '255.255.255.128'),
(26, '255.255.255.192'),
(27, '255.255.255.224'),
(28, '255.255.255.240'),
(29, '255.255.255.248'),
(30, '255.255.255.252'),
(31, '255.255.255.254'),
(32, '255.255.255.255')
) AS o (i, m)
WHERE
i = @mask
SELECT
@result = IIF(Count(*) = 4, 1, 0)
FROM
(
SELECT
*,
IIF(
o_ip BETWEEN o_subnet
AND o_broadcast,
1,
0
) AS eq
FROM
(
SELECT
*,
o_net & o_mask AS o_subnet,
o_net | (255 - o_mask) AS o_broadcast
FROM
(
SELECT
o_net,
o_mask,
o_ip
FROM
(
VALUES
(1, CAST(PARSENAME(@net, 4) AS INTEGER)),
(2, CAST(PARSENAME(@net, 3) AS INTEGER)),
(3, CAST(PARSENAME(@net, 2) AS INTEGER)),
(4, CAST(PARSENAME(@net, 1) AS INTEGER))
) AS c1 (i, o_net)
LEFT JOIN (
SELECT
i,
o_mask
FROM
(
VALUES
(1, CAST(PARSENAME(@mask, 4) AS INTEGER)),
(2, CAST(PARSENAME(@mask, 3) AS INTEGER)),
(3, CAST(PARSENAME(@mask, 2) AS INTEGER)),
(4, CAST(PARSENAME(@mask, 1) AS INTEGER))
) AS c2 (i, o_mask)
) AS c2 ON c1.i = c2.i
LEFT JOIN (
SELECT
i,
o_ip
FROM
(
VALUES
(1, CAST(PARSENAME(@ip, 4) AS INTEGER)),
(2, CAST(PARSENAME(@ip, 3) AS INTEGER)),
(3, CAST(PARSENAME(@ip, 2) AS INTEGER)),
(4, CAST(PARSENAME(@ip, 1) AS INTEGER))
) AS c3 (i, o_ip)
) AS c3 ON c1.i = c3.i
) AS t
) AS t
) AS t
WHERE
eq = 1 RETURN @result END
GO
SELECT
dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '255.255.224.0', '192.168.40.1') -- returns 0
SELECT
dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '19', '192.168.40.1') -- returns 0
SELECT
dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '255.255.192.0', '192.168.80.1') -- returns 1
SELECT
dbo.IPv4SubnetContainsIPAddress('192.168.64.0', '18', '192.168.80.1') -- returns 1
这不是答案本身,而是使James S answer中的某个函数更易于阅读和可能更有效的方法。
SQL Server有一个函数来处理从数据库对象名称获取部分。 这些名称有4个部分[Server] .[Database] .[Schema]. [Object]
。 因此,以下代码允许您获取模式名称。 索引从右侧起作用
SELECT PARSENAME('[myServer].[master].[sys].[objects]', 2)
并没有说你不能将其用于IP地址。而且由于它对SQL的操作非常基础,我认为它已经被高度优化了。
CREATE FUNCTION dbo.fnIPtoBigInt
(
@Ipaddress NVARCHAR(15) -- should be in the form '123.123.123.123'
)
RETURNS BIGINT
AS
BEGIN
DECLARE @ipAsBigInt AS BIGINT
SELECT @ipAsBigInt =
(16777216 * (CAST(PARSENAME(@Ipaddress, 4) AS BIGINT)))
+ (65536 * (CAST(PARSENAME(@Ipaddress, 3) AS BIGINT)))
+ (256 * (CAST(PARSENAME(@Ipaddress, 2) AS BIGINT)))
+ (CAST(PARSENAME(@Ipaddress, 1) AS BIGINT))
RETURN @ipAsBigInt
END
GO