SQL 比较字符串结尾的最佳方法，使用 RIGHT、LIKE 或其他？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7394276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to compare the end of a string, use RIGHT, LIKE or other?
提问by Djof
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:
我需要将字符串的结尾与存储过程中可能的结尾列表进行比较。它将被调用很多，并且大约有 10-15 个候选结尾。在这一点上，仅代码解决方案比创建专用于此的表更可取。类似的东西：
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
对，到目前为止我最喜欢的，但这意味着我必须对字符串长度进行硬编码，因此很容易出错。这也意味着多次切割源字符串。
IF ((LEN(@var) >= 3 AND RIGHT(@var, 3) = 'foo')) OR ...LIKE，可能更慢，但更干净
IF (@var LIKE '%foo') OR ...CHARINDEX，很可能较慢，因为它搜索整个字符串
SUBSTRING，很可能相当于 RIGHT 并且更丑
SQLCLR 创建我自己的 ENDSWITH，它可以很快
There might be better ways I don't know of. What do you think?
可能有我不知道的更好的方法。你怎么认为？
回答by Joe Stefanelli
回答by Randy
also, you may use a combination of RIGHTand LENGTHso that you do not hardcode string length.
此外，您可以使用RIGHTand的组合，LENGTH这样您就不会对字符串长度进行硬编码。
回答by Justin King
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.
解析字符串会有开销，最好的建议是 LIKE '%foo' OR 作为选项。某些索引将利用并进行搜索而不是扫描。
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 和 INDEX http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
