22,209
社区成员
发帖
与我相关
我的任务
分享
解决就好。
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('4.43.69.100' ) between dbo.f_IP2Int('4.43.69.96') and dbo.f_IP2Int('4.208.48.255')
print 'OK'
else
Print 'NO'
/*
OK
*/
select * from t where dbo.fn_ip_to_in(ip) between dbo.fn_ip_to_in(ip_start) and dbo.fn_ip_to_in(ip_end)
--select * from t where ip between dbo.fn_ip_to_in(192.168.1.1) and dbo.fn_ip_to_in(192.168.1.255)
CREATE FUNCTION [dbo].[fn_ip_to_int]
(
@ip VARCHAR(20)
)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