title: Match comma-separated values in SQL Server column
body: <p>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.</p>
  
  <p>For more clarification</p>
  
  <pre><code>Row_id        EMAIL_ID
    1           abc@xyz.com,abcabc@xyz.com, abc2@xyz.com
    2           pqr@xyz.com,pqrabc@xyz.com
  </code></pre>
  
  <p>Now If I insert <code>abcabc@xyz.com,free@xyz.com</code>, it will show that value is duplicated.</p>
  
  <p>I did this </p>
  
  <pre><code>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, ',') )
  </code></pre>
  
  <p>Function</p>
  
  <pre><code>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)&lt;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)&gt;0)  
              insert into @temptable(Items) values(rtrim(ltrim(@slice)))       
  
          set @String = right(@String,len(@String) - @idx)       
          if len(@String) = 0 break       
      end   
  return       
  end 
  </code></pre>
  
  <p>It works fine, but query is slow while checking the records around 100000</p>
  
  <p>Can we do the same by wildcard or something else?</p>