顶者有分！！！ 200 分 求 存储过程 －－－－ 分拣电子邮件

[awash]

说明：
1。电子邮件保存在ms sql数据库中。

1) 该数据库有三张表，表结构和字段名称完全相同

t_email 保存电子邮件
t_duplicate 保存重复邮件
t_error 保存错误邮件

2) 表结构如下：
CREATE TABLE [dbo].[t_email] (
[email] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[company] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[country] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[category] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

3）需要分拣的电子邮件保存在email字段

4）t_email表中的记录有100万条。


2。存储过程的功能要求：
1）如果首尾有空字符，要求清除空字符
例如将 " [e-mail] " 处理为 "[e-mail]"
2）有大量重复邮件地址。要求将重复的记录复制到t_duplicate表中，并在原t_email表中删除该条记录。
3）邮件域名不一定是.com, .net 之类结尾。域名来自世界各地，例如 ".it", ".ae", ".se"等。
4）如果不是合法的电子邮件，则将该条记录复制到t_error表中，并在原t_email表中删除该记录。
5）合法电子邮件只能有字母或数字开头，可以包含英文的下划线字符，英文的中划线字符，英文句号字符。

合法邮件的样式：
[e-mail]

例如，以下3个均为合法邮件
[e-mail]
[e-mail]
[e-mail]

以下这些不是合法邮件
john-lee@test_rr.com (邮件用户名可以有下划线，但域名不能有下划线)
[e-mail]
[e-mail]


6）不是每一行都有地址的，有不少记录的email字段是空的（<null>）。则将该条记录复制到t_error表中，并在原t_email表中删除该记录。
7）最后，t_email中剩下的数据，都是清洗过的记录。


3。待清洗的电子邮件存在不少错误，包括：
1）有特殊字符，例如 “/”，分号，中文字符等。
例如：[e-mail]/[e-mail]

类似的情况还有：
a）[e-mail];[e-mail]
b) [e-mail] <[e-mail]>
c) [e-mail]或 [e-mail]

等等，不胜枚举。 这些记录，一律复制到t_error表中


2）非法邮件地址，这样的地址，将该条记录复制到t_error表。例如：邮件“[e-mail]”

3）无符号连写
例如： [e-mail]@advanceplastics.com
这里实际是两个邮件地址，中间有一个　or，这样的地址，将该条记录复制到t_error表。

再例如：[e-mail]@attbi.com
这里也是两个地址，中间也是有一个　“or”。
再例如：[e-mail][e-mail][e-mail]

这样的地址，将该条记录复制到t_error表。

4）有可能是完全没有@字符，例如　braunsberger.at，或者　"1234"这样的数字或一些英文字串。这样的记录，将该条记录复制到t_error表

。




以下是一段email范例：

[e-mail]/[e-mail]

[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
842234
[e-mail]
hello world
[e-mail]　　　
[e-mail]　　　
[e-mail][e-mail][e-mail]
[e-mail]@hotmail.com
[e-mail];[e-mail]
[e-mail]&[e-mail]
[e-mail]/[e-mail]
[e-mail],[e-mail]
[e-mail][e-mail]
[e-mail]@aol.com
[e-mail], [e-mail], [e-mail]　
[e-mail];[e-mail]
[e-mail] <[e-mail]>
[e-mail]或 [e-mail]
<null>
~~~~~
"[e-mail]" or "[e-mail]"
[e-mail]
@.　
[e-mail]　
#[e-mail]　
//[e-mail]　
[e-mail]　
[e-mail]　
[e-mail]
[e-mail]
[e-mail]
[e-mail]


清洗结果如下：

1。保留在t_email表中的有

[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]
[e-mail]


2。保存在t_duplicate表中的有

[e-mail]
[e-mail]
[e-mail]
[e-mail]
（该邮件重复出现5次，故其中4条记录被复制到t_duplicate表中）


3。保存在t_error表中的有

[e-mail]/[e-mail]

842234
hello world 　
[e-mail][e-mail][e-mail]
[e-mail]@hotmail.com
[e-mail];[e-mail]
[e-mail]&[e-mail]
[e-mail]/[e-mail]
[e-mail],[e-mail]
[e-mail][e-mail]
[e-mail]@aol.com
[e-mail], [e-mail], [e-mail]
[e-mail];[e-mail]
[e-mail] <[e-mail]>
[e-mail]或 [e-mail] 　
<null>
~~~~~
"[e-mail]" or "[e-mail]"
@.　
[e-mail]　
#[e-mail]　
//[e-mail]　
[e-mail]　
[e-mail]

[lff642]

友情帮顶

[lijtom]

学习。能力达不到。

[KermitYue]

up

[rock_LL]

顶一顶
好难啊！

[wgzaaa]

今天老大们都在，把问题说说清楚。

[hyde100]

友情帮顶

[wxg22526451]

up

[angelior]

你肯定要分步的。

写这么长。

还不如写出你要实现的技术点。

如，

1）如果首尾有空字符，要求清除空字符
例如将 " [e-mail] " 处理为 "[e-mail]"
2）有大量重复邮件地址。要求将重复的记录复制到t_duplicate表中，并在原t_email表中删除该条记录。

这两个答为:
1) Ltrim 和 RTrim 函数就可
2）insert into t_duplicate (email) select distinct email from t_email 就可

....

[jaksuse]

up

[bing110]

呵呵，帮忙顶一下！

[bobui]

[Quote=引用 7 楼 HEROWANG 的回复:]
太长，头晕
[/Quote]

[bobui]

[Quote=引用 29 楼 Herb2 的回复:]
SQL codecreate function getemail(@m varchar(8000))
returns int
as
begin
declare @mm varchar(8000),@y varchar(8000),@d varchar(8000)
declare @i int
if @m is null return 2
select @mm = rtrim(ltrim(@m))
select @i = charindex('@',@mm)
if @i < 2 then return 2
select @y=right(@mm,@i-1),@d = left(@mm,len(@mm)-@i)
if patindex('[0-9a-zA-Z]',@y)<>1 then return 2
if patindex('…
[/Quote]

[bobui]

主张用正则表达式过滤

[不得闲]

[Quote=引用 8 楼 ybkenan 的回复:]
頂貼接分
[/Quote]

[lijtom]

学习。

[ab5669]

看的晕，顶一下。

[Herb2]

create function getemail(@m varchar(8000))
returns int
as
begin
  declare @mm varchar(8000),@y varchar(8000),@d varchar(8000)
  declare @i int
  if @m is null return 21
  select @mm = rtrim(ltrim(@m))
  select @i = charindex('@',@mm)
  if @i < 2   return 22
  select @y=right(@mm,@i),@d = left(@mm,len(@mm)-@i)
  if patindex('[0-9a-zA-Z]%',@y)<>1   return 23
  if patindex('%^[0-9a-zA-Z.-_]%',@y)>0  return 24
  if patindex('[0-9a-zA-Z]%',@d)<>1  return 25
  if patindex('%^[0-9a-zA-Z.-]%',@d)>0  return 26
  if (select count(*) from mail where rtrim(ltrim(mm))=@mm)>1 return 1
  return 0
end
go

create table mail(mm varchar(100))
insert mail  
      select '842234'
union all  select '[e-mail]' 
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select '[e-mail] '
union all  select 'hello world'
union all  select '[e-mail][e-mail][e-mail] '
union all  select '[e-mail]@hotmail.com'
union all  select '[e-mail];[e-mail]' 
union all  select '[e-mail]&[e-mail]'
union all  select '[e-mail]/[e-mail] '
union all  select '[e-mail],[e-mail] '
union all  select '[e-mail][e-mail] '
union all  select '[e-mail]@aol.com '
union all  select '[e-mail], [e-mail], [e-mail] '
union all  select '[e-mail];[e-mail] '
union all  select '[e-mail] <[e-mail]> '
union all  select '[e-mail]或 [e-mail] 　 '
union all  select '~~~~ '
union all  select '"[e-mail]" or "[e-mail]" '
union all  select '@.　 '
union all  select '[e-mail]　 '
union all  select '#[e-mail]　 '
union all  select '//[e-mail]　 '
union all  select '[e-mail]　 '
union all  select '[e-mail]'
go
set nocount on
select dbo.getemail(mm),* from mail order by dbo.getemail(mm)

go
drop table mail
drop function getemail
/*
            mm                                                                                                   
----------- ---------------------------------------------------------------------------------------------------- 
0           [e-mail] 
0           [e-mail][e-mail][e-mail] 
0           [e-mail]@hotmail.com
0           [e-mail];[e-mail]
0           [e-mail]&[e-mail]
0           [e-mail]/[e-mail] 
0           [e-mail],[e-mail] 
0           [e-mail][e-mail] 
0           [e-mail]@aol.com 
0           [e-mail], [e-mail], [e-mail] 
0           [e-mail];[e-mail] 
0           [e-mail] <[e-mail]> 
0           [e-mail] 
0           [e-mail] 
0           [e-mail] 
0           [e-mail]
0           [e-mail]
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
1           [e-mail] 
22          hello world
22          ~~~~ 
22          842234
22          @.　 
23          [e-mail]　 
23          #[e-mail]　 
23          [e-mail] 
23          [e-mail]或 [e-mail] 　 
25          "[e-mail]" or "[e-mail]" 
25          //[e-mail]　 
25          [e-mail]　 


*/

[Herb2]

  if patindex('[0-9a-zA-Z]%',@y)<>1   return 23
  if patindex('%^[0-9a-zA-Z.-_]%',@y)>0  return 24
  if patindex('[0-9a-zA-Z]%',@d)<>1  return 25
  if patindex('%^[0-9a-zA-Z.-]%',@d)>0  return 26

==>

  if patindex('[0-9a-zA-Z]%',@y)<>1   return 23
  if patindex('%[^0-9a-zA-Z.[-[_]%',@y)>0  return 24
  if patindex('[0-9a-zA-Z]%',@d)<>1  return 25
  if patindex('%[^0-9a-zA-Z.[-]%',@d)>0  return 26

[Herb2]

if patindex('[0-9a-zA-Z]%',@y)<>1   return 23
  if patindex('%^[0-9a-zA-Z.-_]%',@y)>0  return 24
  if patindex('[0-9a-zA-Z]%',@d)<>1  return 25
  if patindex('%^[0-9a-zA-Z.-]%',@d)>0  return 26

===>

if patindex('[0-9a-zA-Z]%',@y)<>1   return 23
  if patindex('%[^0-9a-zA-Z.[-[_]%',@y)>0  return 24
  if patindex('[0-9a-zA-Z]%',@d)<>1  return 25
  if patindex('%[^0-9a-zA-Z.[-]%',@d)>0  return 26

[awash]

结了，多谢大家关注！特别是Herb2 ！！！