如何在 sql server 2005 中使用具有多个值的类似条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4855754/
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
How to use like condition with multiple values in sql server 2005?
提问by User13839404
I need to filter out records based on some text matching in nvarchar(1000) column. Table has more than 400 thousands records and growing. For now, I am using Like condition:-
我需要根据 nvarchar(1000) 列中的某些文本匹配过滤掉记录。表有超过 40 万条记录并且还在不断增长。现在，我正在使用 Like 条件：-
SELECT
*
FROM
table_01
WHERE
Text like '%A1%'
OR Text like '%B1%'
OR Text like '%C1%'
OR Text like '%D1%'
Is there any preferred work around?
有没有首选的解决方法？
回答by John Hartsock
SELECT
*
FROM
table_01
WHERE
Text like '%[A-Z]1%'
This will check if the texts contains A1, B1, C1, D1, ...
这将检查文本是否包含 A1、B1、C1、D1、...
Reference to using the Like Condition in SQL Server
回答by Nusret Zaman
You can try the following if you know the exact position of your sub string:
如果您知道子字符串的确切位置，可以尝试以下操作：
SELECT
*
FROM
table_01
WHERE
SUBSTRING(Text,1,2) in ('B1','C1','D1')
回答by msms
回答by Lamak
If you can create a FULLTEXT INDEXon that column of your table (that assumes a lot of research on performance and space), then you are probably going to see a big improvement on performance on text matching. You can go to this linkto see what FULLTEXT SEARCHis
and this linkto see how to create a FULLTEXT INDEX.
如果您可以FULLTEXT INDEX在表的那一列上创建一个（假设对性能和空间进行了大量研究），那么您可能会看到文本匹配性能的重大改进。您可以转到此链接查看是什么FULLTEXT SEARCH以及此链接查看如何创建FULLTEXT INDEX.
回答by DCShannon
I needed to do this so that I could allow two different databases in a filter for the DatabaseNamecolumn in an SQL Server Profiler Trace Template.
我需要这样做，以便我可以DatabaseName在 SQL Server Profiler 跟踪模板中的列的过滤器中允许两个不同的数据库。
All you can do is fill in the body of a Likeclause.
您所能做的就是填写Like子句的主体。
Using the reference in John Hartscock's answer, I found out that the like clause uses a sort of limited regex pattern.
使用John Hartscock's answer 中的参考，我发现 like 子句使用了一种有限的正则表达式模式。
For the OP's scenario, MSMS has the solution.
对于 OP 的场景，MSMS 有解决方案。
Assuming I want databases ABCOne, ABCTwo, and ABCThree, I come up with what is essentially independent whitelists for each character:
假设我想要数据库 ABCOne、ABCTwo 和 ABCThree，我为每个字符想出了本质上独立的白名单：
Like ABC[OTT][NWH][EOR]%
Which is easily extensible to any set of strings. It won't be ironclad, that last pattern would also match ABCOwe, ABCTnr, or ABCOneHippotamus, but if you're filtering a limited set of possible values there's a good chance you can make it work.
这很容易扩展到任何字符串集。它不会是铁定的，最后一个模式也将匹配 ABCOwe、ABCTnr 或 ABCOneHippotamus，但是如果您要过滤一组有限的可能值，那么您很有可能使其工作。
You could alternatively use the [^]operator to present a blacklist of unacceptable characters.
您也可以使用[^]运算符来显示不可接受字符的黑名单。
