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 bdeclare @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 ip tpip 211.184.1.2,117.*.3.256,202.180.*.1 117.*.3.256
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) end211.184.1.2,117.*.3.256,202.180.*.1
这就是匹配其中的一个,没卡set @ip='117.2.3.256'楼主多结结帖,谢谢
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 goDECLARE @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 syscolumnsIF 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 '不匹配'
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 bdeclare @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
ip tpip
211.184.1.2,117.*.3.256,202.180.*.1 117.*.3.256
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) end211.184.1.2,117.*.3.256,202.180.*.1
这就是匹配其中的一个,没卡set @ip='117.2.3.256'楼主多结结帖,谢谢
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
goDECLARE @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 syscolumnsIF 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 '不匹配'