■根据已有的IP数据库,写了一个自定义函数...... 根据已有的IP数据库,写了一个自定义函数,代码如下: 其中引用了另外一个函数f_trimstr,具体代码请点击这里 CREATE Function f_getipaddress(@address varchar(15),@kind tinyint) --@kind不同,最后结果形式也不同,如果是国外IP,则只有一种形式,@kind只对国内IP有效 --@kind=1,省份+城市+ISP --@kind=2,省份+城市 --@kind=3,省份 returns varchar(50) AS begin declare @address11 varchar(3) declare @address12 varchar(12) declare @address21 varchar(3) declare @address22 varchar(12) declare @address31 varchar(3) declare @address41 varchar(3) declare @ip float declare @ip1 float declare @ip2 float declare @ip3 float declare @ip4 float declare @country varchar(20) declare @province varchar(20) declare @city varchar(20) declare @isp varchar(20) declare @result varchar(50) select @address = dbo.f_trimstr(@address) select @address11 = left(@address,charindex('.',@address)-1) select @address12 = right(@address,len(@address)-charindex('.',@address)) select @address21 = left(@address12,charindex('.',@address12)-1) select @address22 = right(@address12,len(@address12)-charindex('.',@address12)) select @address31 = left(@address22,charindex('.',@address22)-1) select @address41 = right(@address22,len(@address22)-charindex('.',@address22)) select @ip1 = cast(@address11 as float)*256*256*256 select @ip2 = cast(@address21 as float)*256*256 select @ip3 = cast(@address31 as float)*256 select @ip4 = cast(@address41 as float) select @ip = @ip1 + @ip2 + @ip3 + @ip4 select Top 1 @country=country,@province=province,@city=city,@isp=isp from address1 where ip1 <=@ip and ip2 >=@ip order by (ip2-ip1) asc,province desc,city desc,isp desc if @country is not null begin if @province is null select @province = '' if @city is null select @city = '' if @isp is null select @isp = '' if @country <> '中国' select @result = @country else begin if @kind = 1 begin if @province='' and @city = '' and @isp = '' select @result = @country else select @result = @province + @city + @isp end else if @kind = 2 if @province='' and @city='' select @result = '中国' else select @result = @province + @city else if @province='' select @result = '中国' else select @result = @province end end else select @result = '未知地址' return @result end |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 23:29 , Processed in 0.123902 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.