Geek Answers Handbook

Best way to compare end of line, use RIGHT, HOW or another?

I need to compare the end of lines with a list of possible endings in a stored procedure. It will be called a lot, and there are about 10-15 candidates. At the moment, a code-only solution is preferable to creating tables dedicated to this. Something that would look like:

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

I am looking for the best way in terms of speed, but also maintainability. The candidates that I know of are

  • CORRECTLY, my favorite so far, but that means I need to hardcode the length of the string, so it can be error prone. It also means repeatedly cutting the source string.

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

  • LIKE maybe slower but a little cleaner

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

  • CHARINDEX is most likely slower as it searches for an entire string

  • SUBSTRING is most likely equivalent to RIGHT and much more ugly

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

There may be better ways that I don't know about. What do you think?

+8
optimization sql sql-server tsql string-comparison
source share
3 answers

The best way to optimize this for SQL may be to store the string value REVERSE in another column indexed and search on the left using LEFT or LIKE.

+14
source share

You can also use a combination of RIGHT and LENGTH so that you do not encode the length of the string.

+2
source share

Parsing strings will have overhead, the best offer is LIKE '% foo' OR as an option. Certain indexes will take precedence and do a scan search.

You will need to check if it is suitable, but easy to check.

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

+1
source share

More articles:


All Articles