將 IPv4 字串轉換為二進位數值的 SQL Server 函數

需求背景

資料庫中常以字串（如 '172.29.23.2'）儲存 IPv4 位址，查詢、排序、篩選時若能轉為 4 位元組二進位（binary(4)），效率更高且便於位元運算。本文提供可直接部署的純 T-SQL 純量函數。

SQL Server 2000+ 通用函數

CREATE FUNCTION dbo.IPAddrStr2Bin
(
    @strIP VARCHAR(15),
    @Validate BIT = 1
)
RETURNS BINARY(4)
AS
BEGIN
    -- 選擇性驗證格式
    IF @Validate = 1
    BEGIN
        -- 僅允許數字與點
        IF @strIP LIKE '%[^.0-9]%' RETURN NULL;
        -- 必須有 3 個點
        IF LEN(@strIP) - LEN(REPLACE(@strIP, '.', '')) != 3 RETURN NULL;
        -- 每段至少一位數字
        IF @strIP NOT LIKE '%_%.%_%.%_%.%_%' RETURN NULL;
    END

    DECLARE @oct1 BINARY(1), @oct2 BINARY(1), @oct3 BINARY(1), @oct4 BINARY(1);

    -- 解析四個八位元組
    SET @oct1 = CAST(CAST(LEFT(@strIP, CHARINDEX('.', @strIP) - 1) AS INT) AS BINARY(1));
    SET @oct2 = CAST(CAST(SUBSTRING(@strIP,
        CHARINDEX('.', @strIP) + 1,
        CHARINDEX('.', @strIP, CHARINDEX('.', @strIP) + 1) - CHARINDEX('.', @strIP) - 1) AS INT) AS BINARY(1));
    SET @oct3 = CAST(CAST(SUBSTRING(@strIP,
        CHARINDEX('.', @strIP, CHARINDEX('.', @strIP) + 1) + 1,
        (LEN(@strIP) - CHARINDEX('.', REVERSE(@strIP)) + 1) - CHARINDEX('.', @strIP, CHARINDEX('.', @strIP) + 1) - 1) AS INT) AS BINARY(1));
    SET @oct4 = CAST(CAST(RIGHT(@strIP, LEN(@strIP) - (LEN(@strIP) - CHARINDEX('.', REVERSE(@strIP)) + 1)) AS INT) AS BINARY(1));

    -- 數值範圍驗證
    IF @Validate = 1
    BEGIN
        IF NOT (
            @oct1 BETWEEN 0x01 AND 0xFF AND
            @oct2 BETWEEN 0x00 AND 0xFF AND
            @oct3 BETWEEN 0x00 AND 0xFF AND
            @oct4 BETWEEN 0x00 AND 0xFF
        ) RETURN NULL;
    END

    RETURN @oct1 + @oct2 + @oct3 + @oct4;  -- 二進位串聯
END
GO

使用範例

SELECT dbo.IPAddrStr2Bin('172.29.23.2', 0);  -- 返回 0xAC1D1702
SELECT dbo.IPAddrStr2Bin('256.1.1.1', 1);   -- 驗證失敗，返回 NULL

舊版本（SQL Server 7.0/6.5）替代方案：預存程序

若不支援純量函數，可建立同邏輯的預存程序，透過 OUTPUT 參數回傳：

CREATE PROCEDURE dbo.spIPAddrStr2Bin
    @strIP VARCHAR(15),
    @binIP BINARY(4) OUTPUT,
    @Validate BIT = 1
AS
BEGIN
    -- 邏輯同上，最後 SET @binIP = @oct1 + @oct2 + @oct3 + @oct4;
END
GO

應用場景

• IP 範圍查詢：WHERE dbo.IPAddrStr2Bin(ip_col) BETWEEN dbo.IPAddrStr2Bin('192.168.1.0') AND dbo.IPAddrStr2Bin('192.168.1.255')
• 建立計算欄位並建立索引，大幅提升 IP 查詢效能。
• 位元遮罩運算（如判斷同一子網）。