在SQL Server列中匹配逗号分隔值。

I want to insert multiple email into SQL Server table cell and before inserting them I need check whether the values are already there or not.

我想在SQL Server表单元格中插入多个电子邮件，在插入它们之前，我需要检查这些值是否已经存在。

For more clarification

更多的澄清

Row_id        EMAIL_ID
  1           abc@xyz.com,abcabc@xyz.com, abc2@xyz.com
  2           pqr@xyz.com,pqrabc@xyz.com

Now If I insert abcabc@xyz.com,free@xyz.com, it will show that value is duplicated.

现在如果我插入abcabc@xyz.com,free@xyz.com，它会显示这个值是复制的。

I did this

我做这个

select EMAIL_ID
from TR_DEMO
INNER JOIN dbo.split('abcabc@xyz.com,free@xyz.com', ',') s
ON s.items IN (select items from dbo.split(EMAIL_ID, ',') )

Function

函数

CREATE FUNCTION [dbo].[Split]
(
    @String varchar(8000), 
    @Delimiter char(1)
)       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(rtrim(ltrim(@slice)))       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

It works fine, but query is slow while checking the records around 100000

它工作得很好，但是查询速度很慢，检查记录的速度大约是100000

Can we do the same by wildcard or something else?

我们可以用通配符或者别的方法来做吗?

2 个解决方案

#1

There is a trick you can use for comparing against a comma-separated list. First, you'll want to split your input list. Then for each item, you'll want to check the following:

有一个技巧可以用来与逗号分隔的列表进行比较。首先，您需要拆分输入列表。然后，对于每个项目，您需要检查以下内容:

SELECT EMAIL_ID
  FROM TR_DEMO
 WHERE ','+EMAIL_ID+',' LIKE '%,abcabc@xyz.com,%'

Demo: http://www.sqlfiddle.com/#!3/6dd71/2

演示:http://www.sqlfiddle.com/ ! 3/6dd71/2

Here's another example using your SPLIT method to generate the items:

下面是另一个使用分割方法生成项目的例子:

    SELECT EMAIL_ID
      FROM TR_DEMO T
INNER JOIN dbo.split('abcabc@xyz.com,free@xyz.com', ',') s
               ON ','+T.EMAIL_ID+',' LIKE '%,'+s.items+',%'

http://www.sqlfiddle.com/#!3/a9a7b/1

http://www.sqlfiddle.com/ ! 3 / a9a7b / 1

#2

Ok how about this

好吧这个怎么样

declare @EmailsToInsert varchar(max) = 'abcabc@xyz.com,free@xyz.com'

declare @insertdata table (email varchar(max))
insert into @insertdata select items from dbo.split(@EmailsToInsert, ',')

-- remove any trailing spaces
update tr_demo
set email_id = replace(email_id, ' ', '')

update t
set email_id = t.email_id + ',' + i.email
from tr_demo t
inner join @insertdata i on ',' + t.email_id + ',' not like '%,' + i.email + ',%'

Split the string to insert into a temp table (I've used a table variable here).
分割字符串插入到临时表中(我在这里使用了表变量)。
Remove any trailing spaces in the email_id field
删除email_id字段中的任何尾随空格
Update base on joining your data table and the temp table by assuming emails are wrapped with commas - note the commas added to start and end of email_id as part of the join
在加入数据表和临时表的基础上进行更新，假设电子邮件是用逗号包装的——注意，作为连接的一部分，在email_id的开始和结尾添加了逗号

And here's a working example using table var's

这是一个使用表var的工作示例

declare @data table (row_id int, email_id varchar(max))
insert into @data values 
(1, 'abc@xyz.com,abcabc@xyz.com, abc2@xyz.com'),
(2, 'pqr@xyz.com,pqrabc@xyz.com')

declare @EmailsToInsert varchar(max) = 'abcabc@xyz.com,free@xyz.com'

declare @insertdata table (email varchar(max))
insert into @insertdata select items from dbo.split(@EmailsToInsert, ',')

update @data
set email_value = replace(email_value, ' ', '')

update t
set email_value = t.email_value + ',' + i.email
from @data t
inner join @insertdata i on ',' + t.email_value + ',' not like '%,' + i.email + ',%' 

select * from @data

#1