create table plat_uidlist(Uid varchar(10) , Areaid int , State int)
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )
goselect * from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
/*
Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0(所影响的行数为 6 行)
*/drop table plat_uidlist
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )
goselect * from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
/*
Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0(所影响的行数为 6 行)
*/drop table plat_uidlist
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )
go--符合条件的
select * from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
/*
Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0(所影响的行数为 6 行)
*/--不符合条件的
select * from plat_uidlist where Uid not in
(
select Uid from plat_uidlist where
charindex('888' , Uid) > 0 or charindex('666' , Uid) > 0 or charindex('999' , Uid) > 0 or
charindex('AAAA' , Uid) > 0 or
right(Uid,4) = 'ABCD' or right(Uid,4) = 'AABB' or right(Uid,4) = 'ABAB' or right(Uid,4) = 'AAAB' or right(Uid,4) = 'ABBB' or
Uid between '4980000' and '4989999' or
(areaid = 8 and state <> 3)
)
/*
Uid Areaid State
---------- ----------- -----------
2335437 2 2
2335438 8 3
2335439 3 0
1234578 4 0(所影响的行数为 4 行)
*/--drop table plat_uidlist
insert into plat_uidlist values('2335435' ,8 ,0 )
insert into plat_uidlist values('2335436' ,8 ,1 )
insert into plat_uidlist values('2335437' ,2 ,2 )
insert into plat_uidlist values('2335438' ,8 ,3 )
insert into plat_uidlist values('2335439' ,3 ,0 )
insert into plat_uidlist values('2335440' ,8 ,0 )
insert into plat_uidlist values('6677889' ,8 ,0 )
insert into plat_uidlist values('1234578' ,4 ,0 )
insert into plat_uidlist values('4980010' ,8 ,0 )
insert into plat_uidlist values('4980099' ,8 ,0 )select * from plat_uidlist
where
charindex('888',uid)>0 or charindex('666',uid)>0 or charindex('999',uid)>0 --1、 UID中任意位置出现"888"、"666"、"999"的号码预留。
or
right(uid,4)%1111=0 --2、 UID中任意位置出现"AAAA"的号码预留
or
charindex(right(uid,4),'0123456789')>0 or (right(uid,2)%11=0 and left(right(uid,4),2)%11=0) or cast(left(right(uid,4),2) as int )-cast(right(uid,2)as int)=0 or left(right(uid,4),3)%111=0 or right(uid,3)%111=0 --3、 UID后四位号码出现以下排列的预留:"ABCD"、"AABB"、"ABAB"、"AAAB"、"ABBB"。
or
uid between 4980000 and 4989999--4、 4980000 - 4989999 段内UID预留
or
(areaid=8 and [state]<>3 )--5 areaid=8 state <>3 Uid Areaid State
---------- ----------- -----------
2335435 8 0
2335436 8 1
2335440 8 0
6677889 8 0
4980010 8 0
4980099 8 0(6 行受影响)
楼上的方法 用CHARINDEX函数! 虽然很精确 但是我这里不适用
会查询出
UID
0215780
0250986
0260731这结果 不是我的想要的规则啊号码筛选规则
1、 UID中任意位置出现"888"、"666"、"999"的号码预留。
2、 UID中任意位置出现"AAAA"的号码预留。
3、 UID后四位号码出现以下排列的预留:"ABCD"、"AABB"、"ABAB"、"AAAB"、"ABBB"。
4、 4980000 - 4989999 段内UID预留