SQL Server LIKE 包含括号字符
我正在使用 SQL Server 2008。我有一个包含以下列的表：
sampleData (nvarchar(max))
其中一些行中该列的值是格式如下的列表：
["value1","value2","value3"]
我正在尝试编写一个简单的查询，该查询将返回具有如下格式的列表的所有行，只需检测开头括号。
SELECT * from sampleTable where sampleData like '[%'
上面的查询不起作用，因为“[”是一个特殊字符。如何转义括号以便我的查询满足我的要求？
I am using SQL Server 2008. I have a table with the following column:
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. How can I escape the bracket so my query does what I want?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这里需要进一步说明...
如果您想在一组字符中包含括号（或其他特殊符号），则只能选择使用 ESCAPE（因为您已经在使用括号来指示该组）。
另外，您必须指定 ESCAPE 子句，因为没有默认的转义字符（默认情况下它不是反斜杠，正如我首先想到的那样，来自 C 背景）。
例如，如果我想提取其中一列包含一组“可接受”字符之外的任何内容的行，为了论证起见，让我们说字母数字......我们可以这样开始：
所以我们返回任何具有任何字符的内容不在列表中（由于前导插入符号 ^ 字符）。
如果我们想在这组可接受的字符中添加特殊字符，则不能嵌套括号，因此必须使用转义字符，如下所示...
在括号前面（单独）加上反斜杠，表明我们正在使用反斜杠因为转义字符允许我们在指示字符集的功能括号内转义它们。
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).
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:
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...
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.
除了gbn的答案之外，另一种方法是使用
ESCAPE选项：请参阅
LIKETransact-SQL 文档了解详细信息。Aside from gbn's answer, the other method is to use the
ESCAPEoption:See the
LIKETransact-SQL documentation for details.您可以使用
[ ]包围特殊字符（或范围）。SQL Server LIKE 中的“使用通配符作为文字”部分
请参阅 ：您不需要转义右括号...
You use
[ ]to surround a special character (or range).See the section "Using Wildcard Characters As Literals" in SQL Server LIKE
Note: You don't need to escape the closing bracket...