上一篇【用 SQL 語法查詢符合條件範圍的 IP】中的 IP2INT 的 SQL 語法實在是太醜陋了，所以利用 SQL 可呼叫 CLR 的方式來撰寫查詢。
1. 用 C# 撰寫 SQL Function，請自行編譯成 IPUtility.dll
-
using System;
-
using System.Data.SqlTypes;
-
using Microsoft.SqlServer.Server;
-
public class IPUtility
-
{
-
[SqlFunction]
-
public static SqlInt32 IsInRange(SqlString ip, SqlString startIP, SqlString endIP)
-
{
-
if (IPToLong(startIP) <= IPToLong(ip) && IPToLong(ip) <= IPToLong(endIP) )
-
{
-
return 1;
-
}
-
return 0;
-
}
-
private static long IPToLong(SqlString ip)
-
{
-
string [ ] ipClass = ip.ToString ( ).Split ( );
-
string strhexip = string.Format ( "{0:X2}{1:X2}{2:X2}{3:X2}", int.Parse (ipClass[ 0 ] ), int.Parse (ipClass[ 1 ] ), int.Parse (ipClass[ 2 ] ), int.Parse (ipClass[ 3 ] ) );
-
return Convert.ToInt64 (strhexip, 16 );
-
}
-
}
|
2. 在 SQL 引入 IPUtility 並建立 Function
-
-- Enable CLR integration (啟動 SQL Server 2005 CLR 功能)
-
sp_configure 'clr enabled', 1
-
GO
-
RECONFIGURE
-
GO
-
-- Register Assembly (註冊組件)
-
USE master
-
CREATE ASSEMBLY IPUtility
-
FROM 'C:\TMP\IPUtility.dll' -- 請依照 dll 放置位置填寫
-
WITH PERMISSION_SET = Safe
-
GO
-
---- Drop assembly
-
--DROP ASSEMBLY IPUtility
-
--GO
-
-- Create the managed user-defined function
-
USE master
-
GO
-
CREATE FUNCTION dbo.IsInRange
-
(
-
@IP nvarchar( 15 ), @startIP nvarchar( 15 ), @endIP nvarchar( 15 ) -- 需要三參數: 比對 IP, 範圍起始 IP , 範圍結束 IP
-
)
-
RETURNS int
-
AS EXTERNAL NAME IPUtility.IPUtility.IsInRange
-
GO
-
-- Test Run (測試 Function)
-
DECLARE @IPTable TABLE (IP nvarchar( 15 ) )
-
INSERT INTO @IPTable VALUES ( N'172.16.57.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.58.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.59.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.60.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.61.103' )
-
INSERT INTO @IPTable VALUES ( N'172.16.62.103' )
-
SELECT IP
-
FROM @IPTable
-
WHERE dbo.IsInRange(IP, '172.16.58.103', '172.16.61.109' ) = 1
-
GO
|
OUTPUT:
172.16.58.103
172.16.59.103
172.16.60.103
172.16.61.103