title: Best way to compare the end of a string, use RIGHT, LIKE or other?
body: <p>I need to compare the end of strings against a list of possible ending in a stored procedure. It will be called a lot and there are around 10-15 candidate endings. At this point a code-only solution is preferable to creating tables dedicated to this. Something that would be like:</p>
  
  <pre><code>IF (ENDSWITH(@var, 'foo') OR
      ENDSWITH(@var, 'bar') OR
      ENDSWITH(@var, 'badger') OR
      ENDSWITH(@var, 'snake'))
  (
  )
  </code></pre>
  
  <p>I'm looking for the best way in terms of speed, but also maintainability. Candidates that I know of are</p>
  
  <ul>
  <li><p>RIGHT, my favorite so far but it means I have to hardcode the string length, so can be prone to error. It also means cutting the source string many times.</p>
  
  <pre><code>IF ((LEN(@var) &gt;= 3 AND RIGHT(@var, 3) = 'foo')) OR ...
  </code></pre></li>
  <li><p>LIKE, probably slower, but a bit cleaner</p>
  
  <pre><code>IF (@var LIKE '%foo') OR ...
  </code></pre></li>
  <li><p>CHARINDEX, most probably slower since it searches the whole string</p></li>
  <li><p>SUBSTRING, most probably equivalent to RIGHT and much more ugly</p></li>
  <li><p>SQLCLR to create my own ENDSWITH, it can be pretty quick</p></li>
  </ul>
  
  <p>There might be better ways I don't know of. What do you think?</p>