Match Comma-separated Values In Sql Server Column

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. For more clarification Row_id

Solution 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

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

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

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

Solution 2:

Ok how about this

declare@EmailsToInsertvarchar(max) ='abcabc@xyz.com,free@xyz.com'declare@insertdatatable (email varchar(max))
insertinto@insertdataselect items from dbo.split(@EmailsToInsert, ',')

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

update t
set email_id = t.email_id +','+ i.email
from tr_demo t
innerjoin@insertdata i on','+ t.email_id +','notlike'%,'+ 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
• 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

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

declare@datatable (row_id int, email_id varchar(max))
insertinto@datavalues 
(1, 'abc@xyz.com,abcabc@xyz.com, abc2@xyz.com'),
(2, 'pqr@xyz.com,pqrabc@xyz.com')

declare@EmailsToInsertvarchar(max) ='abcabc@xyz.com,free@xyz.com'declare@insertdatatable (email varchar(max))
insertinto@insertdataselect items from dbo.split(@EmailsToInsert, ',')

update@dataset email_value = replace(email_value, ' ', '')

update t
set email_value = t.email_value +','+ i.email
from@data t
innerjoin@insertdata i on','+ t.email_value +','notlike'%,'+ i.email +',%'select*from@data