[MS-SQL] IP Range 얻어오기

출처: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92534

지금은 필요하지 않지만, 웬지 언젠가 꼭 필요할 것만 같은 function 들이군. 그래서 일단 스크랩.

* 사용예
SELECT dbo.fnNetworkRangeLowestIP('192.168.1.5', '255.255.255.240')
SELECT dbo.fnNetworkRangeHighestIP('192.168.1.5', '255.255.255.240')

* fnNetworkRangeLowestIP (IP 범위 중 가장 작은 IP 주소 얻어오는 함수)

CREATE FUNCTION [dbo].[fnNetworkRangeLowestIP]
(
    @NetworkIP VARCHAR(15),
    @SubnetMask VARCHAR(15)
)
RETURNS VARCHAR(15)
AS
BEGIN
    RETURN CAST(CAST(PARSENAME(@NetworkIP, 4) AS TINYINT) & CAST(PARSENAME(@SubnetMask, 4) AS TINYINT) AS VARCHAR(3))
     + '.' + CAST(CAST(PARSENAME(@NetworkIP, 3) AS TINYINT) & CAST(PARSENAME(@SubnetMask, 3) AS TINYINT) AS VARCHAR(3))
     + '.' + CAST(CAST(PARSENAME(@NetworkIP, 2) AS TINYINT) & CAST(PARSENAME(@SubnetMask, 2) AS TINYINT) AS VARCHAR(3))
     + '.' + CAST(CAST(PARSENAME(@NetworkIP, 1) AS TINYINT) & CAST(PARSENAME(@SubnetMask, 1) AS TINYINT) AS VARCHAR(3))
END

* fnNetworkRangeHighestIP (IP 범위 중 가장 큰 IP 주소 얻어오는 함수)

CREATE FUNCTION [dbo].[fnNetworkRangeHighestIP]
(
    @NetworkIP VARCHAR(15),
    @SubnetMask VARCHAR(15)
)
RETURNS VARCHAR(15)
AS
BEGIN
    RETURN CAST(CAST(PARSENAME(@NetworkIP, 4) AS TINYINT) | (CAST(PARSENAME(@SubnetMask, 4) AS TINYINT) ^ 0xFF) AS VARCHAR(3))
        + '.' + CAST(CAST(PARSENAME(@NetworkIP, 3) AS TINYINT) | (CAST(PARSENAME(@SubnetMask, 3) AS TINYINT) ^ 0xFF) AS VARCHAR(3))
        + '.' + CAST(CAST(PARSENAME(@NetworkIP, 2) AS TINYINT) | (CAST(PARSENAME(@SubnetMask, 2) AS TINYINT) ^ 0xFF) AS VARCHAR(3))
        + '.' + CAST(CAST(PARSENAME(@NetworkIP, 1) AS TINYINT) | (CAST(PARSENAME(@SubnetMask, 1) AS TINYINT) ^ 0xFF) AS VARCHAR(3))
END

- 2011.05.19 Joshua95