TSQL – IPAddrInt , IpAddrStr

CREATE FUNCTION [dbo].[HexStrToVarBinary](@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr)

    set @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]'))
     BEGIN
        SET @hex = SUBSTRING(@hexstr, @i, 1)
        SET @a = @a +
    convert(bigint, CASE WHEN @hex LIKE '[0-9]'
         THEN CAST(@hex as int)
         ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
    set @place = @place * convert(bigint,16)
        SET @i = @i - 1
    
     END

    RETURN convert(varbinary(8000),@a)
END



CREATE FUNCTION [dbo].[IPAddrInt] (@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)) * 65536 +
         CONVERT(BIGINT, PARSENAME(@IP,4)) * 16777216)
 
END



USE [WAN]


CREATE FUNCTION [dbo].[IpAddrStr] (@IP AS BIGINT)
    RETURNS VARCHAR(15)
    AS
    BEGIN
     DECLARE @Octet1 BIGINT
     DECLARE @Octet2 TINYINT
     DECLARE @Octet3 TINYINT
     DECLARE @Octet4 TINYINT
     DECLARE @RestOfIP BIGINT
     
     SET @Octet1 = @IP / 16777216
     SET @RestOfIP = @IP - (@Octet1 * 16777216)
     SET @Octet2 = @RestOfIP / 65536
     SET @RestOfIP = @RestOfIP - (@Octet2 * 65536)
     SET @Octet3 = @RestOfIP / 256
     SET @Octet4 = @RestOfIP - (@Octet3 * 256)
     
     RETURN(CONVERT(VARCHAR, @Octet1) + '.' +
            CONVERT(VARCHAR, @Octet2) + '.' +
            CONVERT(VARCHAR, @Octet3) + '.' +
            CONVERT(VARCHAR, @Octet4))
    END








This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s