[SQL] IP 轉 Number, 與 Number 轉 IP 的 function

分析使用者的 IP 是來自哪個國家/城市, 在觀察網路行為上是很重要的一項數據.
有興趣的人可以到這裡找尋一些 IP 與地理資訊的相關資源.
至於 IP 轉換成數字的公式, 可以在 IP address 的 wiki 找到.
以 wiki 中所舉的 172.16.254.1 來說:

1. 將 172 * 16777216 得到 一個 w 值 ( 2^24 = 16777216 )
2. 將 16 * 65536 得到 一個 x 值 ( 2^16 = 65536 )
3. 將 254 * 256 得到 一個 y 值 ( 2^8 = 256 )
4. 將 1 * 1 得到一個 z 值 ( 2^0 = 1 )
5. 最後透過 w + x + y + z 可得到此 IP 所代表的數字 2886794753

反之, 將 2886794753 轉換為 IP 字串的方式如下:

1. 將 2886794753 / 16777216 得到 172
2. 將 (2886794753 % 16777216) / 65536 得到 16
3. 將 ((2886794753 % 16777216) % 65536) / 256 得到 254
4. 將 (((2886794753 % 16777216) % 65536)) % 256 得到 1
5. 最後結果就是 172.16.254.1

因為欲比對的資料是放在資料庫中的,所以將以上的運算轉為兩個資料庫的 function, 以方便後續查詢.

1. 將 IP 轉為 Number 的 dbo.getIPNumber():

   CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
   RETURNS bigint 
   AS
   BEGIN
   
       Declare @part1 bigint, @part2 int, @part3 int, @part4 int
       SELECT @part1=CAST(LEFT(@ip, CHARINDEX ('.' , @ip)-1) as bigint)*16777216, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
       SELECT @part2=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*65536, @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
       SELECT @part3=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*256, @part4=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
       Return @part1+@part2+@part3+@part4
   
   END

2. 將 Number 轉為 IP 的 dbo.getIPString():

   CREATE FUNCTION [dbo].[getIPString] ( @num bigint )
   RETURNS varchar(15) 
   AS
   BEGIN
   
       DECLARE @ip varchar(15)
       SELECT @ip = @num / 16777216, @num=@num % 16777216
       SELECT @ip = @ip + '.' + cast(@num / 65536 as varchar), @num=@num % 65536
       SELECT @ip = @ip + '.' + cast(@num / 256 as varchar), @ip = @ip + '.' + cast(@num % 256 as varchar)
       Return @ip
   
   END

由於 SQL 的 SELECT 給值是從左至右依序進行的, 所以以上兩個寫法都可再精簡成一個 SELECT 將資料都算完.
精簡版如下:

CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
RETURNS bigint 
AS
BEGIN

    Declare @part1 bigint, @part2 int, @part3 int, @part4 int
    SELECT @part1=CAST(LEFT(@ip, CHARINDEX ('.' , @ip)-1) as bigint)*16777216
        , @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
        , @part2=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*65536
        , @ip=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
        , @part3=LEFT(@ip, CHARINDEX ('.' , @ip)-1)*256
        , @part4=RIGHT(@ip, LEN(@ip)-CHARINDEX ('.' , @ip ))
    Return @part1+@part2+@part3+@part4

END

CREATE FUNCTION [dbo].[getIPString] ( @num bigint )
RETURNS varchar(15) 
AS
BEGIN
    DECLARE @ip varchar(15)
    SELECT @ip = @num / 16777216
        , @num=@num % 16777216
        , @ip = @ip + '.' + cast(@num / 65536 as varchar)
        , @num=@num % 65536
        , @ip = @ip + '.' + cast(@num / 256 as varchar)
        , @ip = @ip + '.' + cast(@num % 256 as varchar)
    Return @ip

END

補充: 在 SQL Server 2005 後, 有一個 ParseName 的函式 (說明)
據 MSDN 的說明, 此函式是用在傳回物件名稱的指定部份. 物件的可擷取部份有物件名稱、擁有者名稱、資料庫名稱和伺服器名稱.
由於在資料庫中, 物件結構是 [伺服器名稱].[資料庫名稱].[結構描述名稱].[物件名稱], 剛好跟 IP 的結構一樣, 所以我們可以改寫 IP 轉換至 Number 的函式如下.

CREATE FUNCTION [dbo].[getIPNumber] ( @ip varchar(15) )
RETURNS bigint 
AS
BEGIN

  Return CAST(PARSENAME(@ip,4) as bigint)*16777216
   + PARSENAME(@ip,3)*65536
   + PARSENAME(@ip,2)*256
   + PARSENAME(@ip,1)

END