SQL numeric to IP address customization Function

Number-to-IP address user-defined function (Appendix: mutual conversion)

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

-- Author: htl258 (Tony)

-- Date: 2010-06-19 10:34:31

-- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog: http://blog.csdn.net/htl258

-- Subject: Number-to-IP address Function

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

-- Numeric to IP address function:

IF OBJECT_ID ('dbo. fn_IP2Str ')> 0

Drop function dbo. fn_IP2Str

GO

Create function [dbo]. [fn_IP2Str] (

@ InIP BIGINT

)

Returns nvarchar (15)

AS

BEGIN

IF @ InIP IS NULL

RETURN '0. 0.0.0'

DECLARE @ ip BIGINT

SET @ ip = @ InIP

SET @ ip = @ ip + 0x100000000

RETURN

CAST (@ ip & 0xff000000)/0x1000000) as nvarchar (3) + '.' +

CAST (@ ip & 0xff0000)/0x10000) as nvarchar (3) + '.' +

CAST (@ ip & 0xff00)/0x100) as nvarchar (3) + '.' +

CAST (@ ip & 0xff) as nvarchar (3 ))

END

GO

 

-- Call example:

SELECT dbo. fn_IP2Str (4294967295)

/*

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

255.255.255.255

 

(One row is affected)

*/

SELECT dbo. fn_IP2Str (0)

/*

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

0.0.0.0

 

(One row is affected)

*/

 

 

-- Appendix

-- SQL functions for converting IP addresses and numbers

If exists (

SELECT *

FROM dbo. sysobjects

WHERE id = OBJECT_ID (n' [dbo]. [f_IP2Int] ')

AND xtype IN (n'fn ', n'if', n'tf ')

)

Drop function [dbo]. [f_IP2Int]

GO

/* -- Convert a balanced IP address to a digital IP Address

-- Producer build 2004.08 (reference please keep this information )--*/

/* -- Call example

Select dbo. f_IP2Int ('2017. 00000000255 ')

Select dbo. f_IP2Int ('12. 168.0.1 ')

--*/

Create function f_IP2Int

(

@ Ip CHAR (15)

)

RETURNS BIGINT

AS

BEGIN

DECLARE @ re BIGINT

SET @ re = 0

SELECT @ re = @ re + LEFT (@ ip, CHARINDEX ('. ', @ ip + '. ')-1) * ID, @ ip = STUFF (@ ip, 1, CHARINDEX ('. ', @ ip + '. '),'')

FROM (

Select id = CAST (16777216 as bigint)

Join all select 65536

Join all select 256

Union all select 1

)

RETURN (@ re)

END

GO

If exists (

SELECT *

FROM dbo. sysobjects

WHERE id = OBJECT_ID (n' [dbo]. [f_Int2IP] ')

AND xtype IN (n'fn ', n'if', n'tf ')

)

Drop function [dbo]. [f_Int2IP]

GO

/* -- Convert a Digital IP address to a formatted IP Address

-- Producer build 2004.08 (reference please keep this information )--

*/

 

/* -- Call example

Select dbo. f_Int2IP (4294967295)

Select dbo. f_Int2IP (212336641)

--*/

Create function f_Int2IP

(

@ IP BIGINT

)

Returns varchar (15)

AS

BEGIN

DECLARE @ re VARCHAR (16)

SET @ re =''

SELECT @ re = @ re + '.' + CAST (@ IP/id as varchar), @ IP = @ IP % ID

FROM (

Select id = CAST (16777216 as bigint)

Join all select 65536

Join all select 256

Union all select 1

)

RETURN (STUFF (@ re, 1, 1 ,''))

END

GO