title: Multiple LIKE statements in TSQL
body: <p>This is what I'm trying to achieve in it's simplest form:</p>
  
  <pre><code>SELECT 
      p.ProductId,
      p.ProductName,
      p.SKU
  FROM tbl_Product p
  WHERE (p.ProductName LIKE '%white%' OR p.SKU LIKE '%white%')
  AND (p.ProductName LIKE '%cup%' OR p.SKU LIKE '%cup%')
  </code></pre>
  
  <p>I'm trying to do this in a UDF, which accepts a comma separated parameter of all the search terms. </p>
  
  <p>I tried splitting that parameter into a temporary table and trying a join, like this:</p>
  
  <pre><code>DECLARE @SearchText nvarchar(1000) SELECT @SearchText='white,cup'
  
  DECLARE @SearchTerms TABLE (String nvarchar(200))
  INSERT INTO @SearchTerms (String)
  SELECT '%' + String + '%' FROM dbo.CsvSplitString(@SearchText)
  
  SELECT 
      p.ProductId,
      p.ProductName,
      p.SKU
  FROM tbl_Product p
  JOIN @SearchTerms s ON (p.ProductName LIKE s.String OR p.SKU LIKE s.String)
  </code></pre>
  
  <p>But that doesn't return what I want - it returns any records where  the Name or SKU matches either of the search terms. I need it to return like the first query, where the Name or SKU matches all of the search terms (I think that makes sense).</p>
  
  <p>Would be massively appreciative of a push in the right direction - let me know if you need me to be more specific.</p>
  
  <p>Note: full text searching is not a viable option at the moment.</p>
  
  <p>Thanks!</p>