如何在 SQL Server 中转义单引号？

How do I escape a single quote in SQL Server?

提问by tim_wonil

I'm trying to insertsome text data into a table in SQL Server9.

我正在尝试将insert一些文本数据放入SQL Server9 中的表格中。

The text includes a single quote(').

文本包含一个单引号 (')。

How do I escape that?

我该如何逃避？

I tried using two single quotes, but it threw me some errors.

我尝试使用两个单引号，但它给我带来了一些错误。

eg. insert into my_table values('hi, my name''s tim.');

例如。 insert into my_table values('hi, my name''s tim.');

回答by C???

Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:

单引号通过将它们加倍来转义，就像您在示例中向我们展示的那样。以下 SQL 说明了此功能。我在 SQL Server 2008 上对其进行了测试：

DECLARE @my_table TABLE (
    [value] VARCHAR(200)
)

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table

Results

结果

value
==================
hi, my name's tim.

回答by Brad Waite

If escaping your single quote with another single quote isn't working for you (like it didn't for one of my recent REPLACE()queries), you can use SET QUOTED_IDENTIFIER OFFbefore your query, then SET QUOTED_IDENTIFIER ONafter your query.

如果用另一个单引号转义单引号对您不起作用（就像我最近的一个REPLACE()查询一样），您可以SET QUOTED_IDENTIFIER OFF在查询之前使用，然后SET QUOTED_IDENTIFIER ON在查询之后使用。

For example

例如

SET QUOTED_IDENTIFIER OFF;

UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S");

SET QUOTED_IDENTIFIER ON;
-- set OFF then ON again

回答by PaulMcG

How about:

怎么样：

insert into my_table values('hi, my name'+char(39)+'s tim.')

回答by Alex Martelli

The doubling up of the quote should have worked, so it's peculiar that it didn't work for you; however, an alternative is using double quote characters, instead of single ones, around the string. I.e.,

报价的加倍应该有效，所以奇怪的是它对你不起作用；但是，另一种方法是在字符串周围使用双引号字符而不是单引号字符。IE，

insert into my_table values("hi, my name's tim.");

insert into my_table values("hi, my name's tim.");

回答by Xin

2 ways to work around this:

解决此问题的 2 种方法：

---

---

for 'you can simply double it in the string, e.g. select 'I''m happpy'-- will get: I'm happy

因为'您可以简单地在字符串select 'I''m happpy'中将其加倍，例如 -- 将得到：I'm happy

---

---

For any charactor you are not sure of: in sql server you can get any char's unicode by select unicode(':')(you keep the number)

对于您不确定的任何字符：在 sql server 中，您可以获得任何字符的 unicode select unicode(':')（您保留数字）

So this case you can also select 'I'+nchar(39)+'m happpy'

所以这种情况下你也可以 select 'I'+nchar(39)+'m happpy'

回答by Daniel Schmidt

Also another thing to be careful of is whether or not it is really stored as a classic ASCII ' (ASCII 27) or Unicode 2019 (which looks similar, but not the same).

This isn't a big deal on inserts, but it can mean the world on selects and updates.
If it's the unicode value then escaping the ' in a WHERE clause (e.g where blah = 'Workers''s Comp') will return like the value you are searching for isn't there if the ' in "Worker's Comp" is actually the unicode value.

If your client application supports free-key, as well as copy and paste based input, it could be Unicode in some rows, and ASCII in others!

A simple way to confirm this is by doing some kind of open ended query that will bring back the value you are searching for, and then copy and paste that into notepad++ or some other unicode supporting editor.

The differing appearance between the ascii value and the unicode one should be obvious to the eyes, but if you lean towards the anal, it will show up as 27 (ascii) or 92 (unicode) in a hex editor.

另外要注意的另一件事是它是否真的存储为经典的 ASCII ' (ASCII 27) 或 Unicode 2019（看起来相似，但不相同）。

这对插入来说没什么大不了的，但它可能意味着选择和更新的世界。
如果它是 unicode 值，则在 WHERE 子句中转义 '（例如 where blah = 'Workers''s Comp'）将返回就像您正在搜索的值不存在一样，如果“Worker's Comp”中的 ' 实际上是Unicode 值。

如果您的客户端应用程序支持自由键以及基于复制和粘贴的输入，则某些行可能是 Unicode，而其他行可能是 ASCII！

确认这一点的一种简单方法是执行某种开放式查询，该查询将带回您正在搜索的值，然后将其复制并粘贴到记事本++或其他一些支持 Unicode 的编辑器中。

ascii 值和 unicode 值之间的不同外观对眼睛来说应该是显而易见的，但是如果你靠近肛门，它会在十六进制编辑器中显示为 27 (ascii) 或 92 (unicode)。

回答by Arulmouzhi

Many of us know that the Popular Method of Escaping Single Quotes is by Doubling them up easily like below.

我们中的许多人都知道转义单引号的流行方法是将它们轻松加倍，如下所示。

PRINT 'It''s me, Arul.';

we are going to look on some other alternate ways of escaping the single quotes.

我们将研究一些其他转义单引号的替代方法。

1.UNICODE Characters

1.UNICODE字符

39 is the UNICODE character of Single Quote. So we can use it like below.

39 是单引号的 UNICODE 字符。所以我们可以像下面那样使用它。

PRINT 'Hi,it'+CHAR(39)+'s Arul.';
PRINT 'Helo,it'+NCHAR(39)+'s Arul.';

2.QUOTED_IDENTIFIER

2.QUOTED_IDENTIFIER

Another simple and best alternate solution is to use QUOTED_IDENTIFIER. When QUOTED_IDENTIFIER is set to OFF, the strings can be enclosed in double quotes. In this scenario, we don't need to escape single quotes. So,this way would be very helpful while using lot of string values with single quotes. It will be very much helpful while using so many lines of INSERT/UPDATE scripts where column values having single quotes.

另一个简单且最佳的替代解决方案是使用 QUOTED_IDENTIFIER。当 QUOTED_IDENTIFIER 设置为 OFF 时，字符串可以用双引号括起来。在这种情况下，我们不需要转义单引号。因此，在使用大量带单引号的字符串值时，这种方式将非常有用。使用如此多行的 INSERT/UPDATE 脚本（其中列值具有单引号）将非常有帮助。

SET QUOTED_IDENTIFIER OFF;
PRINT "It's Arul."
SET QUOTED_IDENTIFIER ON;

CONCLUSION

结论

The above mentioned methods are applicable to both AZURE and On Premises .

上述方法适用于 AZURE 和 On Premises 。

回答by laxman Thapa

Just insert a ' before anything to be inserted. It will be like a escape character in sqlServer

只需在要插入的任何内容之前插入一个 '。它会像 sqlServer 中的转义字符

Example: When you have a field as, I'm fine. you can do: UPDATE my_table SET row ='I''m fine.';

示例：当你有一个字段时，我很好。你可以这样做：UPDATE my_table SET row ='我很好。';

回答by Popa Alin

The following syntax will escape you ONLY ONE quotation mark:

下面的语法只会让你转义一个引号：

SELECT ''''

The result will be a single quote. Might be very helpful for creating dynamic SQL :).

结果将是单引号。可能对创建动态 SQL 非常有帮助 :)。

回答by Dave Kelly

This should work

这应该工作

DECLARE @singleQuote CHAR 
SET @singleQuote =  CHAR(39)

insert into my_table values('hi, my name'+ @singleQuote +'s tim.')