title: Is it possible to do some kind of loop in a WHERE clause to run multiple PATINDEX searches dynamically?
body: <p>This is not for a production system, but for for an internal tool I work with.. performance doesn't matter much.</p>
  
  <p>Essentially what I'm doing is storing a set of rules in a table that is used to filter out results from another query.  Most of the rules are simple column matches, in which case I can do something like</p>
  
  <pre><code>WHERE somecolumn NOT IN (SELECT rule FROM Rules)
  </code></pre>
  
  <p>But one of the other rules I need to check is a text search, so I would store values like <code>%some%text%</code>.. so I need to accomplish something like:</p>
  
  <pre><code>WHERE
      &lt;for each rule in Rules&gt;
      PATINDEX(rule,sometextcolumn) = 0
      &lt;/for each&gt;
  </code></pre>
  
  <p>There is no sort of <code>IN</code> statement equivalent that I can use to process multiple text searches dynamically.</p>
  
  <p>Any tips?</p>