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('192.168.0.11')
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(3232235531)
select dbo.f_Int2IP(212336641)
--*/
CREATE FUNCTION 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
CREATE FUNCTION FN_iporder(@a varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @b varchar(8000)
SELECT @b='',@a=@a+'.'
WHILE charindex('.',@a)>0
BEGIN
SET @b=@b+right('000'+substring(@a,1,charindex('.',@a)-1),3)
SET @a=right(@a,len(@a)-charindex('.',@a))
END
RETURN @b
END
go
CREATE TABLE #ip(ip varchar(15) )
go
INSERT INTO #ip SELECT '192.168.0.2' UNION SELECT '192.168.10.1'
UNION SELECT '132.232.39.231' UNION SELECT '132.232.8.11'
go
SELECT * FROM #ip
ORDER BY dbo.getorder(ip) ASC
--
132.232.8.11
132.232.39.231
192.168.0.2
192.168.10.1
--创建将字符串转为二进制数的函数
create function f_Char2Binary(@str varchar(8000))
returns varchar(8000)
AS
BEGIN
DECLARE @r varbinary(8000),@i int,@j int
select @r=cast(@str as varbinary(8000)),@i=datalength(@r),@str=''
while @i>0
begin
select @j=substring(@r,@i,1),
@str=substring('0123456789ABCDEF',@j/16+1,1)
+substring('0123456789ABCDEF',@j%16+1,1)
+@str,
@i=@i-1
end
return(@str)
end
go
--创建将IP地址转为十进制数的函数,
create function sp_getIP(@v varchar(20))
returns numeric(24)
as
begin
declare @v2 varchar(40),@n numeric(24)
--自己写用户定义函数
create function f_Char2Binary(@str varchar(8000))
returns varchar(8000)
AS
BEGIN
DECLARE @r varbinary(8000),@i int,@j int
select @r=cast(@str as varbinary(8000)),@i=datalength(@r),@str=''
while @i>0
begin
select @j=substring(@r,@i,1),
@str=substring('0123456789ABCDEF',@j/16+1,1)
+substring('0123456789ABCDEF',@j%16+1,1)
+@str,
@i=@i-1
end
return(@str)
end
go