[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():
   ?

   1 2 3 4 5 6 7 8 9 10 11 12

   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():
   ?

   1 2 3 4 5 6 7 8 9 10 11 12

   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 將資料都算完.
精簡版如下:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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 的函式如下.

?

1 2 3 4 5 6 7 8 9 10 11

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