1 declare @email_source varchar(MAX); --1.原始发件人字段 2 declare @key_name varchar(50); --2.我方卷号或客户代码 3 declare c_cur cursor for select distinct vc_your_email,vc_our_ref_or_code from #ip_special_email_total where vc_your_email like '%@%' order by vc_our_ref_or_code; 4 open c_cur; 5 fetch next from c_cur into @email_source,@key_name; 6 while (@@FETCH_STATUS = 0) 7 begin 8 declare @split_str varchar(50) --分隔符 9 declare @pos int --当前位置 10 declare @split_len int --分隔符长度 11 declare @split_index int --最近的分隔符位置 12 declare @split_last_index int --上次分隔符位置 13 declare @avc_email varchar(500) 14 15 set @split_str = ';' --指定分隔符 16 set @pos = 1 17 set @split_len = len(@split_str) 18 set @split_index = 0 19 set @split_last_index = 0 20 21 while (@pos <= len(@email_source)) 22 begin 23 if @split_index > 0 set @split_last_index = @split_index 24 set @split_index = charindex(@split_str, @email_source, @pos) 25 set @pos = @pos + @split_len 26 if @pos = len(@email_source) set @split_index = @pos + @split_len 27 if @split_index > @split_last_index 28 begin 29 set @avc_email = substring(@email_source,(@split_last_index + @split_len),(@split_index - @split_last_index - @split_len)) 30 if isnull(@avc_email,'')<>'' insert #ip_single_email_total select @avc_email,@key_name 31 end 32 end 33 fetch next from c_cur into @email_source,@key_name; 34 end 35 close c_cur 36 deallocate c_cur