Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 349
Djof Avatar asked Sep 12 '11 21:09

Djof


3 Answers

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.

like image 129
Joe Stefanelli Avatar answered Oct 21 '22 01:10

Joe Stefanelli


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

like image 35
Randy Avatar answered Oct 21 '22 00:10

Randy


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

like image 1
Justin King Avatar answered Oct 21 '22 00:10

Justin King