Best way to compare the end of a string, use RIGHT, LIKE or other?

Question

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:

IF (ENDSWITH(@var, 'foo') OR
    ENDSWITH(@var, 'bar') OR
    ENDSWITH(@var, 'badger') OR
    ENDSWITH(@var, 'snake'))
(
)

I'm looking for the best way in terms of speed, but also maintainability. Candidates that I know of are

• 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.

  IF ((LEN(@var) >= 3 AND RIGHT(@var, 3) = 'foo')) OR ...
  

• LIKE, probably slower, but a bit cleaner

  IF (@var LIKE '%foo') OR ...
  

• CHARINDEX, most probably slower since it searches the whole string

• SUBSTRING, most probably equivalent to RIGHT and much more ugly

• SQLCLR to create my own ENDSWITH, it can be pretty quick

There might be better ways I don't know of. What do you think?

Solution 1

The best way to optimize this for SQL might be to store the REVERSE string value in another column that is indexed and search the left side of that using either LEFT or LIKE.

Solution 2

also, you may use a combination of RIGHT and LENGTH so that you do not hardcode string length.

Solution 3

Parsing strings will have an overhead, best suggestion is the LIKE '%foo' OR as an option. Certain indexes will take advanage and do Seeks over Scans.

You'd have to benchmark to see if suitable but would be easily testable.

Check this out regarding LIKE and INDEX http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx