TSQL IPAddrInt, IpAddrStr

USE [WAN]
GO
/****** Object:  UserDefinedFunction [dbo].[IPAddrInt]    Script Date: 08/29/2011 20:02:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
/****** Object:  UserDefinedFunction [dbo].[IpAddrStr]    Script Date: 08/29/2011 20:03:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 SQL Server 2005. 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