Ip算法匹配

http://topic.csdn.net/u/20080711/15/66639249-52d9-40c6-8f5c-131e49c1a6cf.html

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

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) end

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