मैं एक ऐसा फ़ंक्शन लिखने की कोशिश कर रहा हूं जो सीआईडीआर स्वरूपित आईपी (कोड के नीचे आउटपुट) पर कुछ पता जानकारी आउटपुट करेगा:

create function dbo.ConvertIpToInt (@Ip as varchar(15))
    returns bigint
    as
    begin
        return (convert(bigint, parsename(@Ip, 1)) +
                convert(bigint, parsename(@Ip, 2)) * 256 +
                convert(bigint, parsename(@Ip, 3)) * 256 * 256 +
                convert(bigint, parsename(@Ip, 4)) * 256 * 256 * 256)
    end
go


create function dbo.ConvertIntToIp (@Int bigint) 
    returns varchar(15)
    as 
    begin
        declare
             @IpHex     varchar(8)
            ,@IpDotted  varchar(15)

        select
             @IpHex = substring(convert(varchar(30), master.dbo.fn_varbintohexstr(@Int)), 11, 8)

        select
            @IpDotted = convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 1, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 3, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 5, 2), 2)))) + '.' +
                        convert(varchar(3), convert(int, (convert(varbinary, substring(@IpHex, 7, 2), 2))))
        return @IpDotted
    end
go


create function dbo.GetCidrIpRange (@CidrIp varchar(15))
    returns @result table
    (
        CidrIp      varchar(15) not null,
        Mask        int not null,
        LowRange    varchar(15) not null,
        LowIp       varchar(15) not null,
        HighRange   varchar(15) not null,
        HighIp      varchar(15) not null,
        AddressQty  bigint not null
    )
    as
    begin
        declare @Base       bigint  = cast(4294967295 as bigint)
        declare @Mask       int     = cast(substring(@CidrIp, patindex('%/%' , @CidrIP) + 1, 2) as int)
        declare @Power      bigint  = Power(2.0, 32.0 - @Mask) - 1
        declare @LowRange   bigint  = dbo.ConvertIpToInt(left(@CidrIp, patindex('%/%' , @CidrIp) - 1)) & (@Base ^ @Power)
        declare @HighRange  bigint  = @LowRange + @Power

        insert @result
        select
              CidrIp     = @CidrIp
            , Mask       = @Mask
            , LowRange   = @LowRange
            , LowIp      = dbo.ConvertIntToIp(@LowRange)
            , HighRange  = @HighRange
            , HighIp      = dbo.ConvertIntToIp(@HighRange)
            , AddressQty = convert(bigint, power(2.0, (32.0 - @Mask)))
        return
    end
go

select * from dbo.GetCidrIpRange('195.65.254.11/2');

यह निम्नलिखित आउटपुट करता है:

CidrIp           Mask   LowRange    LowIp      HighRange   HighIp           AddressQty
--------------------------------------------------------------------------------------
195.65.254.11/2  2      3221225472  192.0.0.0  4294967295  255.255.255.255  1073741824

मैं कुछ घंटों से SO और Google ब्राउज़ कर रहा हूं, और मुझे पूरा विश्वास है कि ConvertIpToInt और ConvertIntToIp सही हैं।

हालांकि, मैं निम्नलिखित आउटपुट की उम्मीद कर रहा था:

CidrIp           Mask   LowRange    LowIp         HighRange   HighIp          AddressQty
--------------------------------------------------------------------------------------
195.65.254.11/2  2      3275881985  195.65.254.1  3275882238  195.65.254.254  254

क्या कोई मुझे बता सकता है कि मेरे कोड में गलती कहां है? मैं अपने आप को अंधा घूर रहा हूं और मुझे यह दिखाई नहीं दे रहा है (या मैं गलत समझ रहा हूं कि यह कैसे करना है)।

2
Pr0no 7 फरवरी 2017, 16:12
1
आपकी वर्तमान समस्या नहीं, मेरा मानना ​​है, लेकिन आपका इनपुट डेटा प्रकार बहुत छोटा लगता है - उदा। 172.172.172.172/24, varchar(15) में फ़िट होने के लिए बहुत बड़ा है।
 – 
Damien_The_Unbeliever
7 फरवरी 2017, 16:18
यह कैलकुलेटर बताता है कि आपके कार्य सही ढंग से काम कर रहे हैं: ipaddressguide.com/cidr आपको क्यों लगता है कि आपका आउटपुट गलत है ? यदि आप उस कैलकुलेटर में 195.65.254.11/24 डालते हैं, तो आपको अपने अपेक्षित आउटपुट के समान परिणाम मिलते हैं, जिसमें पता 0 और 255 दोनों शामिल हैं जो AddressQty को 256 से टकराते हैं। , लेकिन वे केवल अंतर हैं।
 – 
iamdave
7 फरवरी 2017, 16:51
मुझे लगता है कि यह गलत है क्योंकि तार्किक रूप से, आईपी श्रेणी का कोई मतलब नहीं है, जैसा कि यहां पुष्टि की गई है: jodies.de/ipcalc?host=195.65.254.11&mask1=24&mask2=
 – 
Pr0no
7 फरवरी 2017, 17:17
एक तरफ, अनिर्दिष्ट master.dbo.fn_varbintohexstr काफी समय से अनावश्यक है। CONVERT BINARY को एक उपयुक्त स्टाइल पैरामीटर पास करके हेक्स स्ट्रिंग्स और इसके विपरीत में बदल सकते हैं। "द्विआधारी शैलियों" के अंतर्गत दस्तावेज़ देखें। (अन्य डेटा को हेक्स स्ट्रिंग्स में कनवर्ट करने के लिए इसे पहले BINARY में बदलना आवश्यक है।)
 – 
Jeroen Mostert
7 फरवरी 2017, 21:55
अच्छी बात। लेकिन मुझे लगता है कि ओपी पहले से ही जान सकता है क्योंकि CONVERT 2 की "शैली" के साथ उस फ़ंक्शन में अगले SELECT कथन में उपयोग किया जाता है। सुनिश्चित नहीं है कि वही उपयोग क्यों लागू नहीं किया गया था, लेकिन इस उदाहरण से यह स्पष्ट होना चाहिए कि यह दोनों दिशाओं में काम करता है: SELECT CONVERT(VARBINARY(30), '0x3032', 1) AS [StringToBinary], CONVERT(VARCHAR(30), 0x3032, 1) AS [BinaryToString];। हालांकि, इसे पूरी तरह से गणित ऑपरेशन के रूप में रखना बेहतर/अधिक कुशल होगा, और मैंने यह दिखाने के लिए अपना जवाब अपडेट कर दिया है कि यह कैसे किया जा सकता है :-)।
 – 
Solomon Rutzky
8 फरवरी 2017, 06:00

1 उत्तर

सबसे बढ़िया उत्तर

http://www.ipaddressguide.com/cidr और http://jodies.de/ipcalc?host=195.65.254.11&mask1=2&mask2=, आपका गणना सही हैं। उन दो साइटों के बीच एकमात्र असहमति यह है कि jodies.de/ipcalc पृष्ठ श्रेणी से निम्नतम और उच्चतम (प्रसारण) IP पते हटा देता है।

मैंने 195.65.254.11/2 और 195.65.254.11/24 दोनों के साथ परीक्षण किया। आपका कोड काम करने के लिए, मुझे dbo.GetCidrIpRang पर इनपुट पैरामीटर विनिर्देश को VARCHAR(20) में बदलना होगा (जैसा कि @Damien_The_Unbeliever द्वारा प्रश्न पर टिप्पणी करें)।

प्रदर्शन के संबंध में दो नोट:

  1. ConvertIpToInt और ConvertIntToIp स्केलर UDFs के लिए, आप क्रमशः INET_AddressToNumber और INET_NumberToAddress फ़ंक्शंस का उपयोग करना बेहतर समझ सकते हैं, जो मुफ़्त संस्करण में शामिल हैं SQL# SQLCLR लाइब्रेरी का (जो मैंने लिखा था, लेकिन हे, फ्री :)। इस अनुशंसा का कारण यह है कि T-SQL UDFs के विपरीत, नियतात्मक SQLCLR UDFs (और ये दोनों हैं) समानांतर योजनाओं को नहीं रोकते हैं।

  2. यदि आप SQLCLR मार्ग पर नहीं जाना चाहते हैं, तो आपको कम से कम ConvertIntToIp फ़ंक्शन को विशुद्ध रूप से गणितीय के रूप में रखना चाहिए। उन सभी रूपांतरणों और सबस्ट्रिंग को करने का कोई कारण नहीं है।

    CREATE FUNCTION dbo.IPNumberToAddress(@IPNumber BIGINT)
    RETURNS VARCHAR(15)
    WITH SCHEMABINDING
    AS
    BEGIN
        DECLARE @Oct1 BIGINT,
               @Oct2 INT,
               @Oct3 INT;
    
        SET @Oct1 = @IPNumber / (256 * 256 * 256);
        SET @IPNumber -= (@Oct1 * (256 * 256 * 256));
    
        SET @Oct2 = @IPNumber / (256 * 256);
        SET @IPNumber -= (@Oct2 * (256 * 256));
    
        SET @Oct3 = @IPNumber / 256;
        SET @IPNumber -= (@Oct3 * 256);
    
        RETURN CONCAT(@Oct1, '.', @Oct2, '.', @Oct3, '.', @IPNumber);
    END;
    GO
    

    और तब:

    SELECT dbo.IPNumberToAddress(3275881995);
    -- 195.65.254.11
    
  3. GetCidrIpRange टीवीएफ के लिए, बेहतर होगा कि आप इसे इनलाइन टीवीएफ में बदल दें। आप निम्नलिखित तरीके से सीटीई के माध्यम से बहु-चरणीय गणनाओं को पूरा कर सकते हैं (आपको इसे थोड़ा साफ करने / इसे समाप्त करने की आवश्यकता होगी):

    WITH cte1 AS
    (
        SELECT 2 AS [Mask] -- replace with real formula
    ), cte2 AS
    (
        SELECT 999 AS [Base], -- replace with real formula
               POWER(2.0, 32.0 - cte1.[Mask]) - 1 AS [Power],
            cte1.[Mask]
        FROM   cte1
    ), cte3 AS
    (
        SELECT SQL#.INET_AddressToNumber(left(@CidrIp, PATINDEX('%/%' , @CidrIp) - 1))
            & (cte2.[Base] ^ cte2.[Power]) AS [LowRange],
            cte2.[Power],
            cte2.[Mask]
        FROM   cte2
    )
    SELECT @CidrIp AS [CidrIp],
            cte3.[Mask],
            cte3.[LowRange],
            SQL#.INET_NumberToAddress(cte3.[LowRange]) AS [LowIp],
            (cte3.[LowRange] + cte3.[Power]) AS [HighRange],
            SQL#.INET_NumberToAddress(cte3.[LowRange] + cte3.[Power]) AS [HighIp],
            CONVERT(BIGINT, POWER(2.0, (32.0 - cte3.[Mask]))) AS [AddressQty]
    FROM   cte3 c;
    
1
Community 23 मई 2017, 14:53