SQL数字转IP地址自定义函数
数字转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.csdn.net/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
