none
關於IP字串比對問題 RRS feed

  • 問題

  • 請問我要下查詢比對IP,但是IP例如有172.18.10.1或是172.18.101.50時要怎麼找出這兩者之間的範圍值呢

     

    目前我可以用SQL指令查出172.18.93.1之172.18.93.30這個區域,但是要如何比對到第三個網段呢?

     

    用T-SQL下指令是這樣查,但是萬一我下指令為172.18.10.1到172.18.101.50就查不出資料了

    Select * From dbo.Software
    Where IP >= '172.16.57.103' And IP <= '172.16.57.109'
    2007年11月15日 上午 02:18

解答

  • 程式碼區塊

    -- 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 (建立 CLR 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

    2007年11月18日 下午 04:11

所有回覆

  • 程式碼區塊

    CREATE FUNCTION IP2INT( @ip nvarchar(15) ) RETURNS bigint AS
    BEGIN
      DECLARE @number bigint
      SET @number = 0
     
      SELECT @number = @number +
             LEFT( @ip, CHARINDEX('.', @ip + '.') -1 ) * t,
                   @ip = STUFF(@ip, 1, CHARINDEX('.', @ip + '.' ), '')
      FROM
      (
        SELECT t = CAST(16777216 AS bigint)
        UNION ALL SELECT 65536
        UNION ALL SELECT 256
        UNION ALL SELECT 1
      ) AS T
     
      RETURN(@number)
    END
    GO

     

    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.IP2INT(ip)

    Between dbo.IP2INT('172.16.58.1') AND dbo.IP2INT('172.16.60.1')

    GO

     

     

    2007年11月15日 上午 03:11
  • 上面那段 IP2INT 的 sql function 有點醜...所以我利用 CLR Assembly 的方式寫了一個 sql function ....

     

    IPUtility.CSS  可自行編譯成 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);
        }
    }

     

     

     

    2007年11月18日 下午 04:04
  • 程式碼區塊

    -- 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 (建立 CLR 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

    2007年11月18日 下午 04:11