NumberToIPString

USE [CM_Supplemental]

 GO

/****** Object: UserDefinedFunction [dbo].[NumberToIPString] Script Date: 7/22/2016 8:41:36 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[NumberToIPString](@biNumber bigint)

 /*******************************************************************

DESCRIPTION: Returns IP address as string

 PARAMETERS:

@biNumber - The bigint that contains IP address base 10

 

RETURNS:

IP converted to string

 

USAGE: SELECT dbo.NumberToIPString(2886871053)

 

AUTHOR: Terence Durning

DATE: 10/18/2005

 MODIFICATION HISTORY:

WHO DATE DESCRIPTION

--- ---------- ---------------------------------------------------

TTD 2005/08/19 New

********************************************************************/

returns nvarchar (50)

 as

begin

declare @A0 bigint

declare @A1 bigint

declare @A2 bigint

declare @A3 bigint

declare @R0 bigint

declare @R1 bigint

declare @rs nvarchar (50)

set @A0 = @biNumber / (256 * 256 * 256)

set @R0 = @biNumber - @A0 * (256 * 256 * 256)

set @A1 = @R0 / (256 * 256)

set @R1 = @R0 - @A1 * (256 * 256)

set @A2 = @R1 / 256

set @A3 = @R1 - @A2 * 256

if (@A0 not between 0 and 255) or (@A1 not between 0 and 255) or

(@A2 not between 0 and 255) or (@A3 not between 0 and 255)

set @rs = null

 else

set @rs = cast (@A0 as nvarchar) + '.'

+ cast (@A1 as nvarchar) + '.'

+ cast (@A2 as nvarchar) + '.'

+ cast (@A3 as nvarchar)

return (@rs)

 end
Advertisements

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