3.4.3 ip地址处理函数.sql

很好的sql 文档,可能邦你成为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

--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.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

--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_Int2IP(
@IP bigint
)RETURNS varchar(15)
AS
BEGIN
	DECLARE @re varchar(15)
	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