SQL LIKE 子句的特殊字符列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/712580/
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
List of special characters for SQL LIKE clause
提问by Jonathan Parker
What is the complete list of all special characters for a SQL (I'm interested in SQL Server but other's would be good too) LIKE clause?
SQL 的所有特殊字符的完整列表是什么（我对 SQL Server 感兴趣，但其他的也会很好）LIKE 子句？
E.g.
例如
SELECT Name FROM Person WHERE Name LIKE '%Jon%'
- %
- _
- [specifier] E.g. [a-z]
- [^specifier]
- ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
- ' characters need to be escaped with ' E.g. they're becomes they''re
- %
- _
- [说明符] 例如 [az]
- [^说明符]
- ESCAPE 子句 例如 %30!%%' ESCAPE '!' 将评估 30% 为真
- ' 字符需要用 ' 转义，例如它们变成了它们
%- Any string of zero or more characters._- Any single character- ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
%- 任何零个或多个字符的字符串。_- 任何单个字符- ESCAPE 子句 例如 %30!%%' ESCAPE '!' 将评估 30% 为真
%- Any string of zero or more characters._- Any single character- ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
%- 任何零个或多个字符的字符串。_- 任何单个字符- ESCAPE 子句 例如 %30!%%' ESCAPE '!' 将评估 30% 为真
Sybase
赛贝斯
- %
- _
- [specifier] E.g. [a-z]
- [^specifier]
- %
- _
- [说明符] 例如 [az]
- [^说明符]
%- Any string of zero or more characters._- Any single characterReference Guide here[PDF]
%- 任何零个或多个字符的字符串。_- 任何单个字符参考指南在这里[PDF]
%- Any string of zero or more characters._- Any single character- ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
%- 任何零个或多个字符的字符串。_- 任何单个字符- ESCAPE 子句 例如 %30!%%' ESCAPE '!' 将评估 30% 为真
- %
- _
- An ESCAPE character only if specified.
- %
- _
- 仅当指定时为ESCAPE 字符。
PostgreSQL also has the SIMILAR TOoperator which adds the following:
PostgreSQL 还具有SIMILAR TO添加以下内容的运算符：
[specifier][^specifier]|- either of two alternatives*- repetition of the previous item zero or more times.+- repetition of the previous item one or more times.()- group items together
[specifier][^specifier]|- 两种选择中的一种*- 重复前一项零次或多次。+- 重复前一项或多次。()- 将项目组合在一起
The idea is to make this a community Wiki that can become a "One stop shop" for this.
我们的想法是让这个社区 Wiki 成为一个“一站式服务点”。
回答by ?a?da? Tekin
For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx:
对于 SQL Server，来自http://msdn.microsoft.com/en-us/library/ms179859.aspx：
% Any string of zero or more characters.
WHERE title LIKE '%computer%'finds all book titles with the word 'computer' anywhere in the book title._ Any single character.
WHERE au_fname LIKE '_ean'finds all four-letter first names that end with ean (Dean, Sean, and so on).[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
WHERE au_lname LIKE '[C-P]arsen'finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
WHERE au_lname LIKE 'de[^l]%'all author last names starting with de and where the following letter is not l.
% 任何零个或多个字符的字符串。
WHERE title LIKE '%computer%'查找书名中任何位置带有“计算机”一词的所有书名。_ 任何单个字符。
WHERE au_fname LIKE '_ean'查找所有以 ean 结尾的四字母名字（Dean、Sean 等）。[ ] 指定范围 ([af]) 或集合 ([abcdef]) 内的任何单个字符。
WHERE au_lname LIKE '[C-P]arsen'查找以 arsen 结尾并以 C 和 P 之间的任何单个字符开头的作者姓氏，例如 Carsen、Larsen、Karsen 等。在范围搜索中，范围内包含的字符可能会根据排序规则的排序规则而有所不同。[^] 任何不在指定范围 ([^af]) 或集合 ([^abcdef]) 内的单个字符。
WHERE au_lname LIKE 'de[^l]%'所有以 de 开头且后面的字母不是 l 的作者姓氏。
回答by bobince
- %
- _
- an ESCAPE character only if specified.
- %
- _
- 仅当指定时为 ESCAPE 字符。
It is disappointing that many databases do not stick to the standard rules and add extra characters, or incorrectly enable ESCAPE with a default value of ‘\' when it is missing. Like we don't already have enough trouble with ‘\'!
令人失望的是，许多数据库不遵守标准规则并添加额外字符，或者在缺少时错误地启用默认值为“\”的 ESCAPE。就像我们对 '\' 的困扰还不够！
It's impossible to write DBMS-independent code here, because you don't know what characters you're going to have to escape, and the standard says you can't escape things that don't need to be escaped. (See section 8.5/General Rules/3.a.ii.)
在这里编写与 DBMS 无关的代码是不可能的，因为你不知道你将不得不转义哪些字符，而且标准说你不能转义不需要转义的东西。（见第 8.5/一般规则/3.a.ii.）
Thank you SQL! gnnn
谢谢SQL！恩恩
回答by bobince
You should add that you have to add an extra ' to escape an exising ' in SQL Server:
您应该补充一点，您必须在 SQL Server 中添加一个额外的 ' 以转义现有的 '：
smith's -> smith''s
史密斯 -> 史密斯
回答by Learning
Sybase :
赛贝：
% : Matches any string of zero or more characters.
_ : Matches a single character.
[specifier] : Brackets enclose ranges or sets, such as [a-f]
or [abcdef].Specifier can take two forms:
rangespec1-rangespec2:
rangespec1 indicates the start of a range of characters.
- is a special character, indicating a range.
rangespec2 indicates the end of a range of characters.
set:
can be composed of any discrete set of values, in any
order, such as [a2bR].The range [a-f], and the
sets [abcdef] and [fcbdae] return the same
set of values.
Specifiers are case-sensitive.
[^specifier] : A caret (^) preceding a specifier indicates
non-inclusion. [^a-f] means "not in the range
a-f"; [^a2bR] means "not a, 2, b, or R."
回答by jpierson
Potential answer for SQL Server
SQL Server 的潜在答案
Interesting I just ran a test using LinqPad with SQL Server which should be just running Linq to SQL underneath and it generates the following SQL statement.
有趣的是，我刚刚使用 LinqPad 和 SQL Server 运行了一个测试，它应该只是在下面运行 Linq to SQL 并生成以下 SQL 语句。
Records .Where(r => r.Name.Contains("lkjwer--_~[]"))
记录 .Where(r => r.Name.Contains("lkjwer--_~[]"))
-- Region Parameters
DECLARE @p0 VarChar(1000) = '%lkjwer--~_~~~[]%'
-- EndRegion
SELECT [t0].[ID], [t0].[Name]
FROM [RECORDS] AS [t0]
WHERE [t0].[Name] LIKE @p0 ESCAPE '~'
So I haven't tested it yet but it looks like potentially the ESCAPE '~'keyword may allow for automatic escaping of a string for use within a like expression.
所以我还没有测试它，但看起来ESCAPE '~'关键字可能允许自动转义字符串以在类似表达式中使用。
