---------判断某IP在某字段内---------------

来源：互联网发布：富士微单相机推荐知乎编辑：程序博客网时间：2024/05/12 11:37

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO

--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
    DECLARE @re bigint
    SET @re=0
    SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
        ,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
    FROM(
        SELECT ID=CAST(16777216 as bigint)
        UNION ALL SELECT 65536
        UNION ALL SELECT 256
        UNION ALL SELECT 1)a
    RETURN(@re)
END

go
if   dbo.f_IP2Int('104.43.69.100' ) between dbo.f_IP2Int('102.10.1.1') and dbo.f_IP2Int('134.10.25.255')
    print 'OK'
else
    Print 'NO'

/*
OK

create function f_checkIP(@IP varchar(20),@IP_ST varchar(20),@IP_ED varchar(20))
returns bit
as
begin
    select @IP    = right('00'+ParseName(@IP,4),3)+'.'+
                    right('00'+ParseName(@IP,3),3)+'.'+
                    right('00'+ParseName(@IP,2),3)+'.'+
                    right('00'+ParseName(@IP,1),3),
           @IP_ST = right('00'+ParseName(@IP_ST,4),3)+'.'+
                    right('00'+ParseName(@IP_ST,3),3)+'.'+
                    right('00'+ParseName(@IP_ST,2),3)+'.'+
                    right('00'+ParseName(@IP_ST,1),3),
           @IP_ED = right('00'+ParseName(@IP_ED,4),3)+'.'+
                    right('00'+ParseName(@IP_ED,3),3)+'.'+
                    right('00'+ParseName(@IP_ED,2),3)+'.'+
                    right('00'+ParseName(@IP_ED,1),3)

    if (@IP between @IP_ST and @IP_ED)
        return 1

    return 0
end
go

select dbo.f_checkIP('4.208.48.215','4.43.69.96','4.208.48.255')
select dbo.f_checkIP('4.43.69.95' ,'4.43.69.96','4.208.48.255')