电子邮箱结尾总结

用正则表达判断格式是否合法:^[a-zA-Z0-9_]+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

 

 

select distinct
substring_index(email,'@',-1)     //截取@后边的
from
table
where

email not like '%@qq.com'

and
email not like '%@163.com'
AND
email not like '%@126.com'
AND
email not like '%@139.com'
AND
email not like '%@sina.com'
AND
email not like '%@yahoo.com'
AND
email not like '%@foxmail.com'
AND
email not like '%@live.com'
AND
email not like '%@windowslive.com'
AND
email not like '%@vip.qq.com'
AND
email not like '%@yahoo.com.cn'
AND
email not like '%@yahoo.cn'
AND
email not like '%@hotmail.com'
AND
email not like '%@outlook.com'
AND
email not like '%@sohu.com'
AND
email not like '%@21cn.com'
AND
email not like '%@live.cn'
AND
email not like '%@aliyun.com'
AND
email not like '%@gz.gov.cn'
AND
email not like '%@gmail.com'
AND
email not like '%@yeah.net'
AND
email not like '%@vivo.com.cn'
AND
email not like '%@bbk.com'
AND
email not like '%@msn.cn'
AND
email not like '%@bbk.com'
AND
email not like '%@163.net'
AND
email not like '%@sina.com.cn'
AND
email not like '%@sina.cn'
AND
email not like '%@126.cn'
AND
email not like '%@139.com'
AND
email not like '%@tom.com'
AND
email not like '%@vip.163.com'
AND
email not like '%vip.126.com'
AND
email not like '%@vip.163.com'

=================================================================

select distinct
substring_index(email,'@',-1)
from
table
where
email like '%@%.net'
or
email like '%@%.com.cn'
or
email like '%@%.cn'
or
email like '%@%.com'

 

SELECT 'bbs.mmmm',SUBSTRING_INDEX(email,'@',-1) mailbox,
COUNT(SUBSTRING_INDEX(email,'@',-1))  
FROM bbs.mmmm WHERE email IS NOT NULL AND email<>'' and LENGTH(email)>0
GROUP BY mailbox  

 

转载于:https://www.cnblogs.com/zigewb/p/4552223.html