Ip算法匹配

wishY 2008-07-11 03:10:59



SELECT  LoginIP.LoginIp

FROM AdminInfo INNER JOIN

      LoginIP ON AdminInfo.AdminID = LoginIP.AdminID

      where AdminInfo.AdminID = @AdminID

结果
LoginIP
-----------------
211.184.1.2,117.*.3.256,202.180.*.1

其中逗号为IP分隔，*号为通配符，怎么确定传进来一Ip是否在这里面.? *位置不确定

...全文

594 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

振乾 2010-08-31

打赏
举报

[Quote=引用 8 楼 chuifengde 的回复:]
SQL code
CREATE FUNCTION GetIP(@ip varchar(20))
RETURNS varchar(20)
AS
BEGIN
DECLARE @IPre varchar(20)

SET @IPre=''
IF ISnumeric(parsename(@ip,4))=1
SET @IPre=RIGHT……
[/Quote]

good

okook 2008-08-03

打赏
举报

不错,学习~~

liuyun1987 2008-08-03

打赏
举报

留个记号

wishY 2008-07-11

打赏
举报

感谢大家，先验证楼上，通过.

hery2002 2008-07-11

打赏
举报

使用函数或者parsename();

utpcb 2008-07-11

打赏
举报

mark揭帖料吧　以上已经写给你了

chuifengde 2008-07-11

打赏
举报

CREATE   FUNCTION GetIP(@ip varchar(20))

RETURNS varchar(20)

AS

BEGIN

	DECLARE @IPre varchar(20)

	

	SET @IPre=''

	IF ISnumeric(parsename(@ip,4))=1

		SET @IPre=RIGHT('000'+parsename(@ip,4),3)+'.'

	IF ISnumeric(parsename(@ip,3))=1

		SET @IPre=@IPre+RIGHT('000'+parsename(@ip,3),3)+'.'

	ELSE 

		SET @IPre=@IPre+'%'+'.'

	IF ISnumeric(parsename(@ip,2))=1

		SET @IPre=@IPre+RIGHT('000'+parsename(@ip,2),3)+'.'

	ELSE 

		SET @IPre=@IPre+'%'+'.'

	IF iSnumeric(parsename(@ip,1))=1

		SET @IPre=@IPre+RIGHT('000'+parsename(@ip,1),3)

	ELSE 

		SET @IPre=@IPre+'%'



	

	RETURN @IPre

END

go



DECLARE @ip varchar(20)

SET @ip='211.184.1.2'--'117.234.3.254'



DECLARE @s varchar(100)

SET @s='211.184.1.2,117.*.3.256,202.180.*.1'

DECLARE @a TABLE(id int IDENTITY(1,1),b int)

INSERT @a SELECT TOP 100 0 FROM syscolumns



IF EXISTS(SELECT 1

	FROM

	(

		SELECT SUBSTRING(@s+',',id,charindex(',',@s+',',id)-id) ip

		FROM @a

		WHERE substring(','+@s,id,1)=','

	)aa WHERE dbo.getip(@ip) LIKE dbo.getip(ip))

	PRINT '匹配'

ELSE

	PRINT '不匹配'

wzy_love_sly 2008-07-11

打赏
举报

[Quote=引用 6 楼 wishY 的回复:]
那如果是传个IP 过去匹配 211.184.1.2,117.*.3.256,202.180.*.1 的其中一个呢
[/Quote]

这就是匹配其中的一个，没卡

set @ip='117.2.3.256'

楼主多结结帖，谢谢

wishY 2008-07-11

打赏
举报

那如果是传个IP 过去匹配 211.184.1.2,117.*.3.256,202.180.*.1 的其中一个呢

wzy_love_sly 2008-07-11

打赏
举报

能结帖不？汗

wzy_love_sly 2008-07-11

打赏
举报

declare @ip varchar(50)

set @ip='117.2.3.256'



select ip from (

SELECT a.*,RIGHT(stuff(ip+',',id,len(ip),''),charindex(',',reverse(stuff(','+ip+',',id,len(ip),'')))) as 'tpip'

FROM tb a

INNER JOIN # b

ON SUBSTRING(ip+',',id,1)=',') t

where 

parsename(@ip,4)=case when parsename(tpip,4)='*' then parsename(@ip,4) else parsename(tpip,4) end

and parsename(@ip,3)=case when parsename(tpip,3)='*' then parsename(@ip,3) else parsename(tpip,3) end

and parsename(@ip,2)=case when parsename(tpip,2)='*' then parsename(@ip,2) else parsename(tpip,2) end

and parsename(@ip,1)=case when parsename(tpip,1)='*' then parsename(@ip,1) else parsename(tpip,1) end

211.184.1.2,117.*.3.256,202.180.*.1

wzy_love_sly 2008-07-11

打赏
举报

create table tb(ip varchar(50))

insert into tb select '211.184.1.2,117.*.3.256,202.180.*.1'



select top 1000 id=identity(int,1,1) into # from sysobjects a,sysobjects b



declare @ip varchar(50)

set @ip='117.5.3.256'



select * from (

SELECT a.*,RIGHT(stuff(ip+',',id,len(ip),''),charindex(',',reverse(stuff(','+ip+',',id,len(ip),'')))) as 'tpip'

FROM tb a

INNER JOIN # b

ON SUBSTRING(ip+',',id,1)=',') t

where 

parsename(@ip,4)=case when parsename(tpip,4)='*' then parsename(@ip,4) else parsename(tpip,4) end

and parsename(@ip,3)=case when parsename(tpip,3)='*' then parsename(@ip,3) else parsename(tpip,3) end

and parsename(@ip,2)=case when parsename(tpip,2)='*' then parsename(@ip,2) else parsename(tpip,2) end

and parsename(@ip,1)=case when parsename(tpip,1)='*' then parsename(@ip,1) else parsename(tpip,1) end