sql server 2005 如何用单引号插入文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/775687/
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
How to insert text with single quotation sql server 2005
提问by
I want to insert text with single quote Eg john's to table in sql server 2005 database
我想在 sql server 2005 数据库中插入带有单引号的文本，例如 john's
回答by Binoj Antony
Escape single quote with an additional single as Kirtan pointed out
And if you are trying to execute a dynamic sql (which is not a good idea in the first place) via sp_executesql then the below code would work for you
正如 Kirtan 指出的那样，用额外的单引号转义单引号
如果您试图通过 sp_executesql 执行动态 sql（这首先不是一个好主意），那么下面的代码对您有用
sp_executesql N'INSERT INTO SomeTable (SomeColumn) VALUES (''John''''s'')'
回答by Brannon
The answer really depends on how you are doing the INSERT.
答案实际上取决于您如何执行INSERT.
If you are specifying a SQL literal then you need to use the double-tick approach:
如果要指定 SQL 文字，则需要使用双勾方法：
-- Direct insert
INSERT INTO Table1 (Column1) VALUES ('John''s')
-- Using a parameter, with a direct insert
DECLARE @Value varchar(50)
SET @Value = 'John''s'
INSERT INTO Table1 (Column1) VALUES (@Value)
-- Using a parameter, with dynamic SQL
DECLARE @Value varchar(50)
SET @Value = 'John''s'
EXEC sp_executesql 'INSERT INTO Table1 (Column1) VALUES (@p1)', '@p1 varchar(50)', @Value
If you are doing the INSERT from code, use parameters:
如果您从代码中执行 INSERT，请使用参数：
// Sample ADO.NET
using (SqlConnection conn = new SqlConnection(connectionString)) {
conn.Open();
using (SqlCommand command = conn.CreateCommand()) {
command.CommandText = "INSERT INTO Table1 (Column1) VALUES (@Value)";
command.Parameters.AddWithValue("@Value", "John's");
command.ExecuteNonQuery();
}
}
If your data contains user-input, direct or indirect, USE PARAMETERS. Parameters protect against SQL Injection attacks. Never ever build up dynamic SQL with user-input.
如果您的数据包含直接或间接的用户输入，请使用参数。参数可防止 SQL 注入攻击。永远不要使用用户输入构建动态 SQL。
回答by jitterska
This worked for me:
这对我有用：
INSERT INTO [TABLE]
VALUES ('text','''test.com''', 1)
Basically, you take the single quote you want to insert and replace it with two. So if you want to insert a string of text ('text') and add single quotes around it, it would be ('''text'''). Hope this helps.
基本上，您将要插入的单引号替换为两个。因此，如果您想插入一串文本 ('text') 并在其周围添加单引号，则应该是 ('''text''')。希望这可以帮助。
回答by Kirtan
INSERT INTO Table1 (Column1) VALUES ('John''s')
Or you can use a stored procedure and pass the parameter as -
或者您可以使用存储过程并将参数传递为 -
usp_Proc1 @Column1 = 'John''s'
If you are using an INSERT query and not a stored procedure, you'll have to escape the quote with two quotes, else its OK if you don't do it.
如果您使用的是 INSERT 查询而不是存储过程，则必须用两个引号转义引号，否则如果不这样做也可以。
回答by Matt
This answer works in SQL Server 2005, 2008, 2012.
此答案适用于 SQL Server 2005、2008、2012。
At times the value has MANY single quotes. Rather than add a single quote next to each single quote as described above with 'John''s'. And there are examples using the REPLACEfunction to handle many single quotes in a value.
有时该值有许多单引号。而不是像上面描述的那样在每个单引号旁边添加一个单引号'John''s'。并且有使用该REPLACE函数处理值中的多个单引号的示例。
Try the following. This is an update statement but you can use it in an INSERTstatement as well.
请尝试以下操作。这是一个更新语句，但您也可以在INSERT语句中使用它。
SET QUOTED_IDENTIFIER OFF
DECLARE @s VARCHAR(1000)
SET @s = "SiteId:'1'; Rvc:'6'; Chk:'1832'; TrEmp:'150'; WsId:'81'; TtlDue:'-9.40'; TtlDsc:'0'; TtlSvc:'0'; TtlTax:'-0.88'; TtlPay:'0'; TipAmt:'0.00'; SvcSeq:'09'; ReTx:'N'; TraceId:'160110124347N091832';"
UPDATE TransactionPaymentPrompt
set PromptData = @s
from TransactionPaymentPrompt tpp with (nolock)
where tpp.TransactionID = '106627343'
回答by JohnB
You asked how to escape an Apostrophe character (')in SQL Server. All the answers above do an excellent job of explaining that.
您询问了如何Apostrophe character (')在 SQL Server 中转义 an 。上面的所有答案都很好地解释了这一点。
However, depending on the situation, the Right single quotation mark character (')might be appropriate.
但是，根据情况，Right single quotation mark character (')可能是合适的。
(No escape characters needed)
（不需要转义字符）
-- Direct insert
INSERT INTO Table1 (Column1) VALUES ('John's')
? Apostrophe (U+0027)
? 撇号 (U+0027)
? Right single quotation mark (U+2019)
? 右单引号 (U+2019)
