SQL server之IP地址计算

DECLARE @ip AS VARCHAR(15) = '1.0.0.0'

-- 将IP地址转换为整数
DECLARE @ipInt AS INT =
(CAST(PARSENAME(@ip, 4) AS INT) * POWER(256, 3)) +
(CAST(PARSENAME(@ip, 3) AS INT) * POWER(256, 2)) +
(CAST(PARSENAME(@ip, 2) AS INT) * POWER(256, 1)) +
CAST(PARSENAME(@ip, 1) AS INT)

-- 计算IP减1的整数值
DECLARE @ipMinusOneInt AS INT = @ipInt- 1

-- 将整数值转换回IP地址
DECLARE @ipMinusOne AS VARCHAR(15) =
CAST((@ipMinusOneInt / POWER(256, 3)) AS VARCHAR) + '.' +
CAST(((@ipMinusOneInt % POWER(256, 3)) / POWER(256, 2)) AS VARCHAR) + '.' +
CAST(((@ipMinusOneInt % POWER(256, 2)) / POWER(256, 1)) AS VARCHAR) + '.' +
CAST(((@ipMinusOneInt % POWER(256, 1))) AS VARCHAR)

SELECT @ipMinusOne AS [IP Minus One]