sqlserver根据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