SQL点分IP转换为整型
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[GetIPShiJinZhi](@ip VARCHAR(32))
RETURNS NVARCHAR(32)
AS
BEGIN
DECLARE @DecimalResult BIGINT --返回的结果
DECLARE @IPIndex BIGINT --截取的IP
DECLARE @num BIGINT --循环的次数
DECLARE @INDEX BIGINT --索引
DECLARE @ZhiShu BIGINT --指数
DECLARE @IPIndexResult BIGINT --转换后的值
SET @IPIndexResult=0
SET @DecimalResult=0
SET @ZhiShu=0
SET @num=1
--截取IP
WHILE (@num<5)
BEGIN
IF (@num=4)
SET @IPIndex=@ip
ELSE
BEGIN
SET @INDEX=PATINDEX('%.%',@ip)--取得.的索引
SET @INDEX=@INDEX-1
SET @IPIndex=SUBSTRING(@ip,1,@INDEX)--取得截取的IP
END
SET @IPIndexResult=@IPIndex --给结果赋值
SET @ZhiShu=32-8*@num
--转换为整数
WHILE(@zhishu>0)
BEGIN
SET @IPIndexResult=@IPIndexResult*2
SET @ZhiShu=@ZhiShu-1
END
SET @num=@num+1
SET @ip=SUBSTRING(@ip,@INDEX+2,LEN(@ip))
SET @DecimalResult=@DecimalResult+@IPIndexResult
END
RETURN @DecimalResult
END