数字转IP地址自定义函数(附：相互转换)

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

-- 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.youkuaiyun.com/htl258

-- Subject: 数字转IP地址函数

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

--数字转IP地址函数：

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

 

--调用示例：

SELECT dbo.fn_IP2Str(4294967295)

/*

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

255.255.255.255

 

(1 行受影响)

*/

SELECT dbo.fn_IP2Str(0)

/*

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

0.0.0.0

 

(1 行受影响)

*/

 

 

--附：以下转自邹建

--ip地址与数字相互转换的sql函数

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  

   

/*--字符型IP 地址转换成数字IP  

   

  --邹建  2004.08(引用请保留此信息)--*/  

   

/*--调用示例 

   

  select dbo.f_IP2Int('255.255.255.255')  

  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)

             UNION ALL   SELECT 65536

             UNION ALL   SELECT 256

             UNION ALL   SELECT 1

         )A

    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  

   

/*--数字  IP   转换成格式化  IP   地址 

   

  --邹建  2004.08(引用请保留此信息)--

*/  

 

/*--调用示例 

   

  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)

             UNION ALL   SELECT 65536

             UNION ALL   SELECT 256

             UNION ALL   SELECT 1

         )a

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

END  

GO