title: Using LIKE and IN and a Subquery in a single SQL Statement
body: <p>I am writing a query in which I am trying to search a subquery/CTE for a wildcard substring, and nesting this logic in my CASE statement.  For example:</p>
  
  <pre><code>SELECT
  CASE 
  WHEN '%' + text + '%' IN (SELECT Column1 FROM Table) THEN 'I am in Column1'
  ELSE text END
  FROM Table
  </code></pre>
  
  <p>Unfortunately, it looks like there is no possibly way to do this.  Since I would need to use the LIKE operator and there is no way to use both LIKE and IN.  I would have to write each LIKE statement separately, and that would be for 1000+ rows.  Does anyone recommend a more immediate solution?  Thanks kindly in advance!</p>
  
  <p>--
  Edit: Sorry, some clarifications per comments.  A better example:</p>
  
  <pre><code>UserID     |  UserPeers   |  Gender
  --------------------------------------------
  Mike       |  Tom1, Bob1  |  M
  John       |  Tom1, Greg1 |  M
  Sally      |Mike1, John1  |  F
  Sara       | Sally1, Bob1 |  F
  </code></pre>
  
  <p>In the above table, I need to search the substrings in UserPeers columns to see if they exist anywhere in the UserID column.  The rows that would be successfully returned in this case would be the ones under Sally and Sara, since 'Mike' and 'Sally' exist under UserID.</p>
  
  <pre><code>SELECT *
  FROM Users
  WHERE '%' + UserPeers + '%' LIKE (SELECT UserID FROM Users)
  </code></pre>
  
  <p>The error returned here is:
      Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>