<< SQL Server - Random Number Generator | 回blog首頁 | SQL Server 2005 - Truncate All Tables >>

2007年11月15日

用 SQL 語法查詢符合條件範圍的 IP

  1. CREATE FUNCTION IP2INT( @ip nvarchar(15) ) RETURNS bigint AS
  2. BEGIN
  3.   DECLARE @number bigint
  4.   SET @number = 0
  6.   SELECT @number = @number +
  7.          LEFT( @ip, CHARINDEX('.', @ip + '.') -1 ) * t,
  8.                @ip = STUFF(@ip, 1, CHARINDEX('.', @ip + '.' ), '')
  9.   FROM
  10.   (
  11.     SELECT t = CAST(16777216 AS bigint)
  12.     UNION ALL SELECT 65536
  13.     UNION ALL SELECT 256
  14.     UNION ALL SELECT 1
  15.   ) AS T
  17.   RETURN(@number)
  18. END
  19. GO
  21. DECLARE @IPTable TABLE (ip nvarchar(15) )
  22. INSERT INTO @IPTable VALUES ( N'172.16.57.103')
  23. INSERT INTO @IPTable VALUES ( N'172.16.58.103')
  24. INSERT INTO @IPTable VALUES ( N'172.16.59.103')
  25. INSERT INTO @IPTable VALUES ( N'172.16.60.103')
  26. INSERT INTO @IPTable VALUES ( N'172.16.61.103')
  27. INSERT INTO @IPTable VALUES ( N'172.16.62.103')
  29. SELECT ip
  30. FROM @IPTable
  31. WHERE dbo.IP2INT(ip)
  32. BETWEEN dbo.IP2INT('172.16.58.1') AND dbo.IP2INT('172.16.60.1')


Posted by chhuang0123 at 樂多Roodo! │11:19 │回應(0) │引用(0) │ADO.NET
樂多分類：網路/3C 工具：編輯本文
Ads by Roodo! 

引用URL

http://cgi.blog.roodo.com/trackback/4483405
  


回最上方