比较字符串结尾，使用RIGHT，LIKE或其他方法的最佳方法? [英] Best way to compare the end of a string, use RIGHT, LIKE or other?

问题描述

我需要将字符串的结尾与存储过程中可能结尾的列表进行比较.这将被称为很多，大约有10-15个候选结局.在这一点上，仅代码解决方案比创建专用于此的表更为可取.会是这样的:

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，最有可能是速度较慢，因为它会搜索整个字符串

CHARINDEX, most probably slower since it searches the whole string

SUBSTRING，很可能等同于RIGHT，而且丑陋得多

SUBSTRING, most probably equivalent to RIGHT and much more ugly

SQLCLR来创建我自己的ENDSWITH，它可以很快

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?

推荐答案

针对SQL优化此方法的最佳方法可能是存储

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.

这篇关于比较字符串结尾，使用RIGHT，LIKE或其他方法的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！