使用方法 select db0. f_getipaddress('127.0.0.1')

CREATE    Function f_getipaddress(@address varchar(15))
returns float
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

return @ip
end

CREATE    Function f_trimstr(@str varchar(100))

returns varchar(100)

AS
--去掉字符串中的所有空格
begin

declare @i int
declare @s1 varchar(50)
declare @result varchar(100)
declare @len int

select @result = ''
select @str = ltrim(rtrim(@str))
select @len = len(@str)
select @i = 1

while @i<=@len
begin
select @s1 = substring(@str,@i,1)
if(@s1<>'')
begin
select @result = @result + @s1
end
select @i = @i + 1
end

return @result

end