根据IP和掩码 计算起止IP的 MSSQL函数

写这段代码的意义

现在需要根据获取的IP，进入数据库查询是否已经存在在。
数据库中存储的是IP和掩码，需要将数据库中的数据，还原为一个IP段，然后和获取的IP对比，求出该IP是否在该段里面。
下面的函数，只是根据IP和掩码，计算起止IP的函数。

需要的依赖函数

首先我们需要几个依赖函数
1.uf_split_string是一个很好用的表值函数，也是一个很通用的函数，可以根据输入的符号，切分字符串变成一个数据表。
这里是用这个函数，根据点（.）将IP且分为四段。

ALTER FUNCTION [dbo].[uf_split_string]
(
	@in_str varchar(MAX),
	@split  varchar(1)
)
RETURNS 
@strs TABLE 
(
	c_row int,
	c_str  varchar(1000)
)
AS
BEGIN			
		select @in_str=ltrim(rtrim(isnull(@in_str,'')))
		if(@in_str='')
		begin
			insert into @strs(c_row,c_str)
				select 1,''
		end	
		DECLARE @splitlen INT,@row int
        SET @splitlen = LEN(@split + 'a') - 2   
		set @row = 1    
        WHILE CHARINDEX(@split, @in_str) > 0
            BEGIN
                INSERT  INTO @strs (c_row, c_str )
                   SELECT  @row,(LEFT(LEFT(@in_str, CHARINDEX(@split, @in_str) - 1), 1000) )
                SET @in_str = STUFF(@in_str, 1, CHARINDEX(@split, @in_str) + @splitlen, '')
				set @row = @row +1
            END
        IF @in_str <> ''
            BEGIN 
                INSERT  INTO @strs (c_row, c_str )
                   SELECT  @row,LEFT(@in_str, 1000)
            END          
		return 
end

uf_split_string运算示例

2.uf_BinToInt是将一串不大于8位的01组成的字符串转为数字的函数

ALTER FUNCTION [dbo].[uf_BinToInt] (@BinaryChar char(10))  
RETURNS int 
AS  
BEGIN 
    DECLARE @stringLength int,@ReturnValue int,@Index int
    DECLARE @CurrentChar char(1)
    SET @Index = 0
    SET @ReturnValue = 0
    SET @stringLength = LEN(@BinaryChar)
    While @Index<@stringLength
        BEGIN
            SET @Index = @Index + 1
            SET @CurrentChar = SUBSTRING(@BinaryChar,@Index,1)
            IF(@CurrentChar='1' or @CurrentChar='0')
                BEGIN
                    SET @ReturnValue = @ReturnValue + (CAST(@CurrentChar as int) * POWER(2,@stringLength - @Index))
                END
        END
    RETURN @ReturnValue
END

uf_BinToInt运算示例

3.uf_IntToBin是将不大于256的数字转换为一串01组成的字符串函数

ALTER FUNCTION [dbo].[uf_IntToBin] (@Int int)  
RETURNS varchar(10)
AS  
BEGIN 
	declare @Return varchar(20)
	set @Return = ''
	declare @i int
	while @Int >0
	begin
		set @i = @int%2
		set @Return = cast(@i as char(1)) + @Return
		set @Int = @Int /2
	end

	select @Return = '00000000' + @Return
	select @Return = right(@Return,8)
    RETURN @Return
END

uf_IntToBin运算示例

4.uf_masktochar是将掩码值，也就是24,23这样的数字，转为255.255.255.0,255.255.254.0这样的格式的函数

ALTER FUNCTION [dbo].[uf_masktochar] (@mask int)  
RETURNS varchar(20)
AS  
BEGIN 
	declare @i int,@ret varchar(20),@Return varchar(20)
	declare @j int = 4
	select @ret = ''
	select @Return = ''
	while @j >0
	begin
		if(@mask > 8)
		begin
			set @Return = @Return + '255.'
		end
		else
		begin
			---set @i = @mask%8
			set @ret = ''
			set @i = @mask
			if @i < 0 set @i = 0
			while @i >0
			begin
				set @ret = @ret + '1'
				set @i = @i -1
			end
			set  @ret = @ret + '00000000'
			set  @ret = left(@ret,8)
			select @Return= @Return + cast(dbo.uf_BinToint(@ret)  as varchar(5)) + '.'
		end
		set @mask = @mask -8
		set @j = @j - 1
	end
	select @Return = substring(@Return,1,len(@Return)-1)
    RETURN @Return
END

uf_masktochar运算示例

5.uf_MaskExtent是一个核心运算函数，它将一个数字，如1，和掩码252进行掩码运算，计算出来该段的起止数字

ALTER FUNCTION [dbo].[uf_MaskExtent] (@int int,@mask int,@type varchar(10))
RETURNS int
AS
BEGIN
	declare @intchar char(10), @maskchar char(10)
	select  @intchar = dbo.uf_IntToBin(@int)
	select  @maskchar = dbo.uf_IntToBin(@mask)
	declare @i int = 8
	declare @intc char(1), @maskc char(1),@ints char(10) = '',@inte char(10) = ''
	while @i > 0
	begin
		set @intc = substring(@intchar,@i,1)
		set @maskc = substring(@maskchar,@i,1)
		if @maskc = '0'
			begin
				set @ints = '0' + @ints
				set @inte = '1' + @inte 
			end
		if @maskc = '1'
			begin
				set @ints = @intc + @ints
				set @inte = @intc + @inte 
			end
		set @i = @i-1
	end

	if @type = 'start'
		set @int =  dbo.uf_BinToInt(@ints)
	if @type = 'end'
		set @int =  dbo.uf_BinToInt(@inte)
	return @int
end

uf_MaskExtent运算示例

最终计算函数

uf_iptomark是最终计算IP和掩码对应的IP段的函数

ALTER FUNCTION [dbo].[uf_iptomark] (@ip varchar(20),@mask int,@type varchar(10))
RETURNS varchar(20)
AS
BEGIN
	declare @i int
	declare @iptomark table
	(c_row int,
	c_ip int,
	c_mask int,
	c_ips int,
	c_ipe int)

	declare @maskchar varchar(20)

	set @maskchar = dbo.uf_masktochar(@mask)

	insert into @iptomark(c_row,c_ip,c_mask)
	select a.c_row,a.c_str,b.c_str from dbo.uf_split_string(@ip,'.') a ,dbo.uf_split_string(@maskchar,'.')  b
	where a.c_row = b.c_row

	 update @iptomark set c_ips  =  dbo.uf_MaskExtent(c_ip,c_mask,'start'),
							c_ipe  =  dbo.uf_MaskExtent(c_ip,c_mask,'end')
					
	declare @ipchar varchar(20) = ''

	if @type = 'start'
	begin
		set @i = 4
		while @i >0
		begin
			select  @ipchar = cast(c_ips as varchar(10))+ '.' + @ipchar
				from @iptomark
					where c_row = @i
			set @i = @i -1
		end
	end
	if @type = 'end'
	begin
		set @i = 4
		while @i >0
		begin
			select  @ipchar = cast(c_ipe as varchar(10))+ '.' + @ipchar
				from @iptomark
					where c_row = @i
			set @i = @i -1
		end
	end
		select @ipchar = substring(@ipchar,1,len(@ipchar)-1)
	return @ipchar
end

uf_iptomark运算演示