SQL Server LIKE 包含括号字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3661125/
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
SQL Server LIKE containing bracket characters
提问by CJS
Using SQL Server 2008. I have a table with the following column:
使用 SQL Server 2008。我有一个包含以下列的表：
sampleData (nvarchar(max))
The value for this column in some of these rows are lists formatted as follows:
其中一些行中此列的值是格式如下的列表：
["value1","value2","value3"]
I'm trying to write a simple query that will return all rows with lists formatted like this, by just detecting the opening bracket.
我正在尝试编写一个简单的查询，该查询将通过仅检测左括号来返回具有这样格式的列表的所有行。
SELECT * from sampleTable where sampleData like '[%'
The above query doesn't work because '[' is a special character, and I can't for the life of me figure out how to escape the bracket so my query does what I want.
上面的查询不起作用，因为 '[' 是一个特殊字符，我一生都无法弄清楚如何转义括号，因此我的查询可以满足我的要求。
Thanks for any suggestions!
感谢您的任何建议！
回答by gbn
... like '[[]%'
You use [ ]to surround a special character (or range)
你[ ]用来包围一个特殊字符（或范围）
See the section "Using Wildcard Characters As Literals" in SQL Server LIKE
请参阅SQL Server LIKE 中的“使用通配符作为文字”部分
Edit, 24 Nov 2011
编辑，2011 年 11 月 24 日
Note: You don't need to escape the closing bracket...
注意：您不需要转义结束括号...
回答by Ed Harper
Aside from gbn's answer, the other method is to use the ESCAPEoption:
除了 gbn 的回答，另一种方法是使用该ESCAPE选项：
SELECT * from sampleTable where sampleData like '\[%' ESCAPE '\'
See the documentationfor details
有关详细信息，请参阅文档
回答by KeithFearnley
Just a further note here... If you want to include the bracket (or other specials) within a set of characters, you only have the option of using ESCAPE (since you are already using the brackets to indicate the set). Also you MUST specify the ESCAPE clause, since there is no default escape character (it isn't backslash by default as I first thought, coming from a C background).
在这里再说明一下...如果您想在一组字符中包含括号（或其他特殊字符），您只能选择使用 ESCAPE（因为您已经使用括号来表示该字符集）。此外，您必须指定 ESCAPE 子句，因为没有默认转义字符（默认情况下它不是反斜杠，正如我最初想到的那样，来自 C 背景）。
e.g. if I want to pull out rows where a column contains anything outside of a set of 'acceptable' characters, for the sake of argument let's say alphanumerics... we might start with this
例如，如果我想提取列包含一组“可接受”字符之外的任何内容的行，为了论证起见，让我们说字母数字……我们可以从这个开始
SELECT * FROM MyTest WHERE MyCol LIKE '%[^a-zA-Z0-9]%'
So we are returning anything that has any character not in the list (due to the leading caret ^ character).
因此，我们将返回包含不在列表中的任何字符的任何内容（由于前导插入符 ^ 字符）。
If we then want to add special characters in this set of acceptable characters, we cannot nest the brackets so we must use an escape character, like this...
如果我们想在这组可接受的字符中添加特殊字符，我们不能嵌套括号，所以我们必须使用转义字符，像这样......
SELECT * FROM MyTest WHERE MyCol LIKE '%[^a-zA-Z0-9\[\]]%' ESCAPE '\'
Preceding the brackets (individually) with a backslash and indicating that we are using backslash for the escape character allows us to escape them within the functioning brackets indicating the set of characters.
在括号（单独）前加上反斜杠并指示我们使用反斜杠作为转义字符允许我们在指示字符集的功能括号内对它们进行转义。
Sorry for the dumb example, but hope it helps someone
抱歉这个愚蠢的例子，但希望它可以帮助某人
