-->生成测试数据
declare @tb table([phoneno] nvarchar(20))
Insert @tb
select 13008762222 union all
select 13008764444 union all
select 13008763344 union all
select 13008763355 union all
select 13008762233 union all
select 13008761234 union all
select 13008765677
Select * from @tb where ( replicate(right([phoneno],2),2) = right([phoneno],4) )
or
(
(replicate(right([phoneno],1),2) = right([phoneno],2))
and
(
ascii(substring([phoneno],len([phoneno])-3,1)) = ascii(substring([phoneno],len([phoneno])-2,1))
)
)
/*
phoneno
--------------------
13008762222
13008764444
13008763344
13008763355
13008762233(5 row(s) affected)
*/
declare @tb table([phoneno] nvarchar(20))
Insert @tb
select 13008762222 union all
select 13008764444 union all
select 13008763344 union all
select 13008763355 union all
select 13008762233 union all
select 13008761234 union all
select 13008765677
Select * from @tb where ( replicate(right([phoneno],2),2) = right([phoneno],4) )
or
(
(replicate(right([phoneno],1),2) = right([phoneno],2))
and
(
ascii(substring([phoneno],len([phoneno])-3,1)) = ascii(substring([phoneno],len([phoneno])-2,1))
)
)
/*
phoneno
--------------------
13008762222
13008764444
13008763344
13008763355
13008762233(5 row(s) affected)
*/
Insert @tb
select 13008762222 union all
select 13008764444 union all
select 13008763344 union all
select 13008763355 union all
select 13008762233 union all
select 13008761234 union all
select 13008765677select * from @tb where substring(phoneno,8,1)=substring(phoneno,9,1) and substring(phoneno,10,1)=substring(phoneno,11,1) and left(phoneno,7)='1300876'/*
phoneno
--------------------
13008762222
13008764444
13008763344
13008763355
13008762233(5 行受影响)*/
select * from tb where right(phoneno,4)>'9999'