[SQL][Script]利用使用者定義函數解決 IP 比對的問題
晚上跟方丈討論一個利用資料庫來做統計分析的處理，但要判斷某個 IP 是否在一個區間範圍內，因此 SQL 並沒有內建的類似函數，而如果用 Like 去判斷，很多狀況又沒有辦法來判斷，因此決定在 SQL Server 寫一些使用者定義函數來解決這個部份的處理，因此參考方丈所提供的範例，我把他改寫了一份，先整理出來，如果以後有機會用到的話那就可以直接拿來用了。
這個函數主要是運用 PARSENAME 這個函數來進行拆解 IP 的四個數值部份，再透過 POWER 函數計算次方計算，將 IP 換成 10 進位的數值，因為數值很大，所以這裡要配合使用 BIGINT 型態。
CREATE FUNCTION [dbo].[IPAddressConvertInt]( @IPAddress varchar(15) ) RETURNS BIGINT
AS
BEGIN
RETURN (
CAST(PARSENAME(@IPAddress,1) AS BIGINT ) +
CAST(PARSENAME(@IPAddress,2) AS BIGINT ) * Power(256,1)+
CAST(PARSENAME(@IPAddress,3) AS BIGINT ) * Power(256,2)+
CAST(PARSENAME(@IPAddress,4) AS BIGINT ) * Power(256,3) )
END
因此搭配上述的函數，我們就可以簡化比對的方式，因此如果要判斷某個 IP 是否落在某個特定 IP 區間的話，我們可以搭配以下的函數來做判斷
CREATE FUNCTION [dbo].[IsIPAddressInRange]
(
@IPAddress varchar(15), @StartRange varchar(15), @EndRange varchar(15)
)
RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN dbo.IPAddressConvertInt(@IPAddress) BETWEEN dbo.IPAddressConvertInt(@StartRange) AND dbo.IPAddressConvertInt(@EndRange) THEN 1
ELSE 0
END
END
但有些時候我們 IP 不是用區間，而是用 CIDR 的方式去表示的話，那麼也可以改成如下的方式
CREATE FUNCTION [dbo].[IsIPAddressInCIDR]
(
@IPAddress varchar(15), @IPRange varchar(20)
)
RETURNS INT
AS
BEGIN
DECLARE @SlashPos INT = CHARINDEX('/', @IPRange);
DECLARE @Network VARCHAR(12) = SUBSTRING(@IPRange, 1, @SlashPos - 1);
DECLARE @PrefixBits INT = CAST(SUBSTRING(@IPRange, @SlashPos + 1, 20) AS INT);
DECLARE @Mask BIGINT = POWER(CAST(2 AS BIGINT), 32) - POWER(CAST(2 AS BIGINT), 32 - @PrefixBits);
RETURN
CASE
WHEN dbo.IPAddressConvertInt(@IPAddress) & @Mask = dbo.IPAddressConvertInt(@Network) & @Mask THEN 1
ELSE 0
END
END
以下是簡單的使用範例
SELECT dbo.IPAddressConvertInt( '192.168.3.233' )
SELECT dbo.IsIPAddressInRange( '192.168.3.233', '192.168.2.1' , '192.168.4.254' )
SELECT dbo.IsIPAddressInCIDR ( '192.168.3.233', '192.168.2.0/23' )
之前沒有想到類似的問題，也謝謝方丈的案例分享，讓我多學到一些方式…
