IP 주소 범위 체크



-- 숫차형 IP주소를 IP Address로 변환하는 함수 작성

CREATE FUNCTION dbo.Func_ConvertNumlp(@ip BIGINT)

RETURNS VARCHAR(15)

AS

BEGIN

    DECLARE @delimiter VARCHAR(1)

        ,@SUBNET_MASK INT

        ,@idx  INT

        ,@ipSeg  BIGINT

        ,@segments VARCHAR(15)

       

    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 1

    SET @ipSeg = 0

    SET @segments = ''

    

    WHILE (@idx <= 4)

    BEGIN

        SET @ipSeg = CAST(@ip / POWER(@SUBNET_MASK, 4-@idx) AS BIGINT)

        SET @ip = @ip - CAST(@ipSeg * POWER(@SUBNET_MASK, 4-@idx) AS BIGINT)

        IF (@idx = 1)

            SET @segments = CAST(@ipSeg AS VARCHAR)

        ELSE

            SET @segments = @segments + @delimiter + CAST(@ipSeg AS VARCHAR)

            SET @idx = @idx + 1 

    END

    

    RETURN @segments  

END

GO

--IP Address를 숫자형 IP주소로 변환하는 함수 작성

CREATE FUNCTION dbo.Func_ConvertIpNum(@ip NVARCHAR(15))

RETURNS BIGINT

AS

BEGIN

    DECLARE @delimiter NVARCHAR(1)

        ,@SUBNET_MASK   INT

        ,@idx INT

        ,@ipNum FLOAT

        ,@textXML XML

       

    DECLARE @segments TABLE (id  INT, col   INT)

    

    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 4

    SET @ipNum = 0

    SELECT @textXML = CAST('<col>' + REPLACE(@ip, @delimiter, '</col><col>') + '</col>' AS XML);

    

    INSERT INTO @segments (id, col)

    SELECT ROW_NUMBER() OVER (ORDER BY col) AS id

        , T.col.value('.', 'int') AS col

    FROM @textXML.nodes('/col') T(col)

    ORDER BY id DESC

    

    SELECT @ipNum = @ipNum + (CAST((col % @SUBNET_MASK) AS FLOAT) * POWER(@SUBNET_MASK, @idx-id))

    FROM @segments

    

    RETURN CAST(@ipNum AS BIGINT)      

END

GO

CREATE TABLE ipcountry

(

    startip VARCHAR(15),

    endip VARCHAR(15),

    startipnum   BIGINT,

    endipnum    BIGINT,

    country   VARCHAR(2),

    city VARCHAR(20)

);

GO

INSERT INTO ipcountry VALUES ('192.168.0.1', '192.168.10.23', 3232235521, 3232238103, 'KR', 'Seoul');

INSERT INTO ipcountry VALUES ('192.168.10.24', '192.168.101.255', 3232238104, 3232261631, 'US', 'New York');

INSERT INTO ipcountry VALUES ('192.168.102.1', '192.168.102.10', 3232261633, 3232261642, 'CN', 'Beijing');

GO

SELECT * FROM ipCountry

WHERE dbo.Func_ConvertlpNum('192.168.0.40') BETWEEN startipnum AND endipnum

-출처 : 태기의 데이터베이스 때려잡기