MySQL 在 SQL 'LIKE' 命令中转义单引号的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3504342/
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
Ways to escape single quotes in SQL 'LIKE' command
提问by Egalitarian
What are the various ways to ESCAPE single quotes(') in the SQL LIKEcommand?
SQLLIKE命令中ESCAPE 单引号（'）的各种方法有哪些？
One way is to put two single quotes whenever you have to escape a single quote.
一种方法是在必须转义单引号时放置两个单引号。
Can you people suggest something?
你能提出一些建议吗？
Databases: SQL Server 2005and Oracle 10g
数据库：SQL Server 2005和 Oracle 10g
回答by Justin Niessner
You already have the answer. You have to use two single quotes:
你已经有了答案。您必须使用两个单引号：
select * from table where field like '%''something''%'
回答by Floyd
The best way is to bind the parameter with ADO or ADO.NET.
最好的方法是将参数与 ADO 或 ADO.NET 绑定。
Like (example in C# with ADO.NET):
像（在 C# 中使用 ADO.NET 的示例）：
SqlCommand x = new SqlCommand(sqlConnection, @"select * from table where col like '%'+@para1+'%'");
x.parameters.add(new SqlParameter("@para1",sqltype.varchar,100)).value = "this is a' test";
In SQL Server 2005you escape a single quote (') with a double single quote ('') if you do not want to bind:
在SQL Server 2005 中，如果您不想绑定，可以使用双单引号 ('') 转义单引号 (')：
select * from table where col like '%this is a'' test%'
回答by p.campbell
Two single quotes is the best solution.
两个单引号是最好的解决方案。
Alternatively, you can use a CHAR(39)to represent a single quote character.
或者，您可以使用 aCHAR(39)来表示单引号字符。
UPDATE Employee SET LastName = 'O' + CHAR(39) + 'Brien'
WHERE ID=1;
回答by Tony Andrews
There is also the "Q-quote" method:
还有“Q-quote”方法：
select * from mytable where text like q'#%Here's some text%#';
This is available since Oracle 10.2.
这从 Oracle 10.2 开始可用。
I used a '#' character as the quote delimiter, but you can use pretty much any character that won't appear in the string (there are a few exceptions, such as the space character).
我使用了“#”字符作为引号分隔符，但是您几乎可以使用任何不会出现在字符串中的字符（有一些例外，例如空格字符）。
In a simple example like that above I probably wouldn't do this. I'd just double up the single quotes, but it does come in handy when building large dynamic SQL statements that include lots of string literals.
在像上面这样的简单示例中，我可能不会这样做。我只是将单引号加倍，但在构建包含大量字符串文字的大型动态 SQL 语句时它确实派上用场。
