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