数据库中查询出无效的电话号码

[yuhouqingchen2020]

表如下：
name tel
a1 0
a2 11111111111
a3 22222222222
a4 19000000000
a5 186482939911
a6 18648393991
a7 18648393991
a8 0
a9 18648493998
a10 04713530188
查询结果
name tel
a1 0
a2 11111111111
a3 22222222222
a4 19000000000
a5 186482939911
a6 18648393991
a7 18648393991
a8 0

[卖水果的net]

这是什么规则呢？

[二月十六]

这个在程序中用正则处理更好

[yuhouqingchen2020]

因为程序开始的时候就没有处理，所以现在想过滤一下。就是重复的手机号和不是11位数字的（除了固定电话）查询出来

[不穿内裤的超人]

with cte (name, tel)as (
select 'a1','0' union 
select 'a2','11111111111'  union  
select 'a3','22222222222' union 
select 'a4','19000000000' union 
select 'a5','186482939911' union 
select 'a6','18648393991' union 
select 'a7','18648393991' union 
select 'a8','0' union 
select 'a9','18648493998' union 
select 'a10','04713530188'
)
select * from cte where LEN(tel)<>11 union 
select * from cte where SUBSTRING(tel,1,1) not in ('0','1') union 
select * from cte a where exists (select tel,COUNT(*) from cte
where a.tel=cte.tel group by tel having COUNT(*)>1)

order by name

A2和A4无法判断，难道判断某个字符重复出现频率过高。比如8次以上？

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([name] nvarchar(23),[tel] nvarchar(32))
Insert #T
select N'a1',N'0' union all
select N'a2',N'11111111111' union all
select N'a3',N'22222222222' union all
select N'a4',N'19000000000' union all
select N'a5',N'186482939911' union all
select N'a6',N'18648393991' union all
select N'a7',N'18648393991' union all
select N'a8',N'0' union all
select N'a9',N'18648493998' union all
select N'a10',N'04713530188'
Go
--测试数据结束
SELECT DISTINCT
        tel
FROM    #T
WHERE   LEN(tel) = 11
        AND ( tel LIKE '13%'
              OR tel LIKE '15%'
              OR tel LIKE '17%'
              OR tel LIKE '18%'
              OR tel LIKE '0%'
            ) 

[0与1之间]

select * from tab where patindex('%[^0-9]%',tel)>0 select * from tab where len(tel)<>11 select * from tab a where exists(select 1 from tab where tel=a.tel group by tel having count(1)>1) 不过固话的匹配应该有标准，例如区号

[yuhouqingchen2020]

感谢楼上的。