SQL Server 自定義函數：IP 字符串轉數值（IPv4），便於範圍查詢與排序

IP 地址 a.b.c.d 可轉為 32 位無符號整數：((a*256 + b)*256 + c)*256 + d。數值形式利於 BETWEEN 範圍查詢、排序、索引命中。

函數代碼

CREATE FUNCTION dbo.IP2Number (@ip VARCHAR(16))
RETURNS BIGINT
AS
BEGIN
  SET @ip = @ip + '.';
  DECLARE @pos TINYINT, @num BIGINT, @bin INT, @off INT;
  SET @bin = 16777216; -- 256^3
  SET @off = 0;
  SET @num = 0;
  WHILE @bin >= 1
  BEGIN
    SET @pos = @off + 1;
    SET @off = CHARINDEX('.', @ip, @pos);
    SET @num = @num + CONVERT(INT, SUBSTRING(@ip, @pos, @off - @pos)) * @bin;
    SET @bin = @bin / 256;
  END
  RETURN @num;
END;

使用示例

SELECT dbo.IP2Number('192.168.1.100'); -- 返回 3232235876
SELECT * FROM IpTable WHERE dbo.IP2Number(IP) BETWEEN dbo.IP2Number('192.168.1.1') AND dbo.IP2Number('192.168.1.254');

注意事項

• 僅支持 IPv4，IPv6 需 128 位（可用 VARBINARY(16) 存儲）
• SQL Server 2016+ 可用 PARSENAME 簡化分段：PARSENAME(@ip,4)*16777216 + PARSENAME(@ip,3)*65536 + PARSENAME(@ip,2)*256 + PARSENAME(@ip,1)
• 大表頻繁查詢建議添加計算列 + 索引：ALTER TABLE t ADD IPNum AS dbo.IP2Number(IP) PERSISTED; CREATE INDEX IX_IPNum ON t(IPNum);