sql语句判断某IP在某字段内的方法
如何用sql语句判断某IP在某字段内?
例子:
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
判断IP地址是否在字段中的调用示例: