SQL 查询访问中带有撇号的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6764197/
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 to Query text in access with an apostrophe in it
提问by Kevin
Please help me with this because I cannot seem to get it right
请帮我解决这个问题，因为我似乎无法做对
I am trying to query a name(Daniel O'Neal) in column names tblStudents in an access database however access reports a syntax error with the statement:
我正在尝试在访问数据库中的列名 tblStudents 中查询名称（Daniel O'Neal），但是访问报告语句出现语法错误：
Select * from tblStudents where name like 'Daniel O'Neal'
because of the apostrophe in the name.
因为名字中的撇号。
How do I overcome this.
我该如何克服这一点。
Thank you in advance
先感谢您
回答by Alex K.
You escape 'by doubling it, so:
你'通过加倍逃脱，所以：
Select * from tblStudents where name like 'Daniel O''Neal'
Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.
请注意，如果您从用户输入中接受“Daniel O'Neal”，则损坏的引用是一个严重的安全问题。您应该始终清理字符串或使用参数化查询。
回答by HansUp
When you include a string literal in a query, you can enclose the string in either single or double quotes; Access' database engine will accept either. So double quotes will avoid the problem with a string which contains a single quote.
当您在查询中包含字符串文字时，您可以将字符串括在单引号或双引号中；Access 的数据库引擎将接受。所以双引号将避免包含单引号的字符串的问题。
SELECT * FROM tblStudents WHERE [name] Like "Daniel O'Neal";
If you want to keep the single quotes around your string, you can double up the single quote within it, as mentioned in other answers.
如果您想在字符串周围保留单引号，则可以将其中的单引号加倍，如其他答案中所述。
SELECT * FROM tblStudents WHERE [name] Like 'Daniel O''Neal';
Notice the square brackets surrounding name. I used the brackets to lessen the chance of confusing the database engine because nameis a reserved word.
注意name周围的方括号。我使用括号来减少混淆数据库引擎的机会，因为name是一个保留字。
It's not clear why you're using the Likecomparison in your query. Based on what you've shown, this should work instead.
不清楚您为什么在查询中使用Like比较。根据您所展示的内容，这应该起作用。
SELECT * FROM tblStudents WHERE [name] = "Daniel O'Neal";
回答by murgatroid99
Escape the apostrophe in O'Nealby writing O''Neal(two apostrophes).
O'Neal通过书写O''Neal（两个撇号）来转义撇号。
回答by Ralf
...better is declare the name as varible ,and ask before if thereis a apostrophe in the string:
...更好的是将名称声明为变量，并先询问字符串中是否有撇号：
e.g.:
例如：
DIM YourName string
DIM 你的名字字符串
YourName = "Daniel O'Neal"
你的名字 = "丹尼尔·奥尼尔"
If InStr(YourName, "'") Then
SELECT * FROM tblStudents WHERE [name] Like """ Your Name """ ;
else
SELECT * FROM tblStudents WHERE [name] Like '" Your Name "' ;
endif
回答by user9198383
How about more simply: Select * from tblStudents where [name] = replace(YourName,"'","''")
如何更简单： Select * from tblStudents where [name] = replace(YourName,"'","''")
