SQL查询访问中的文本，其中包含撇号

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

先谢谢你

5 个解决方案

#1

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”，那么破坏的报价是一个严重的安全问题。您应该始终清理字符串或使用参数化查询。

#2

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 name is a reserved word.

注意名称周围的方括号。我使用括号来减少混淆数据库引擎的可能性，因为name是一个保留字。

It's not clear why you're using the Like comparison in your query. Based on what you've shown, this should work instead.

目前尚不清楚为什么在查询中使用Like比较。根据你所展示的内容，这应该适用。

SELECT * FROM tblStudents WHERE [name] = "Daniel O'Neal";

#3

Escape the apostrophe in O'Neal by writing O''Neal (two apostrophes).

通过写O''Neal（两个撇号）来逃避奥尼尔的撇号。

#4

...better is declare the name as varible ,and ask before if thereis a apostrophe in the string:

...更好的是将名称声明为变量，并在之前询问字符串中是否有撇号：

e.g.:

例如。：

DIM YourName string

DIM YourName字符串

YourName = "Daniel O'Neal"

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

#5

How about more simply: Select * from tblStudents where [name] = replace(YourName,"'","''")

如何更简单：从tblStudents中选择*，其中[name] = replace（YourName，“'”，“''”）

#1