T-SQL中是否有LIKE语句的替代方法？ [英] Is there and alternative to LIKE statement in T-SQL?

问题描述

我有一个场景，需要执行以下操作：

I have a scenario where I need to perform following operation:

SELECT *
FROM
[dbo].[MyTable]
WHERE
[Url] LIKE '%<some url>%';

我必须在网址的开头和结尾使用两个％（通配符）（'％<某些url>％'），即使用户键入部分文字，用户也应该能够搜索完整的url。例如，如果url为 http://www.google.co.in 且用户类型为 goo ，则该网址必须出现在搜索结果中。 Like 运算符引起性能问题。 我需要一个替代方法，以便摆脱该声明和通配符。换句话说，在这种情况下我不想使用LIKE语句。我尝试使用T-SQL CONTAINS ，但是它不能解决我的问题。除了可以执行模式匹配并快速向我提供结果之外，还有其他可用的选择吗？

I have to use two % (wildcard characters) at the beginning and the end of Url ('%<some url>%') as user should be able to search the complete url even if he types partial text. For example, if url is http://www.google.co.in and user types "goo", then the url must appear in search results. LIKE operator is causing performance issues. I need an alternative so that I can get rid of this statement and wildcards. In other words, I don't want to use LIKE statement in this scenario. I tried using T-SQL CONTAINS but it is not solving my problem. Is there any other alternative available than can perform pattern matching and provide me results quickly?

推荐答案

要以％开始引起扫描。没有绕过它。它必须评估每个值。

Starting a like with a % is going to cause a scan. No getting around it. It has to evaluate every value.

如果对列进行索引，则应该是索引（而不是表）扫描。

If you index the column it should be an index (rather than table) scan.

您不这样做

Charindex和patindex是替代项，但仍会扫描而不解决性能问题。

You don't have an alternative that will not cause a scan.
Charindex and patindex are alternatives but will still scan and not fix the performance issue.

能否将组件分解到一个单独的表中？

www

google

co

在

Could you break the components out into a separate table?
www
google
co
in

然后像'goo％'一样搜索？

这将使用索引，因为它没有开始

And then search on like 'goo%'?
That would use an index as it does not start with %.

更好，但是您可以在 google上搜索并获得索引搜索。

Better yet you could search on 'google' and get an index seek.

希望该表中的字符串具有唯一性，并且在Int PK上具有单独的联接，因此它不会返回例如多个www。

And you would want to have the string unique in that table with a separate join on Int PK so it does not return multiple www for instance.

怀疑FullText Contains的速度并不快，因为FullText将网址保留为一个单词。

Suspect FullText Contains was not faster because FullText kept the URL as one word.

这篇关于T-SQL中是否有LIKE语句的替代方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！