在 SQL 的 Exec 语句中使用单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3364232/
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
Using single quote in an Exec statement in SQL
提问by Mithil
I am having issues executing a SQL statement from a stored proc having single quotes. Here is my query from a stored procedure that I am executing.
我在从具有单引号的存储过程执行 SQL 语句时遇到问题。这是我正在执行的存储过程中的查询。
EXEC('UPDATE myTABLE
SET myCOLUMN = (SELECT Replace('OSINGLEQUOTEJOHN DOE','SINGLEQUOTE','''')')
I am trying to update table "myTABLE" column "myCOLUMN" with a value "O'John Doe"
我正在尝试使用值“O'John Doe”更新表“myTABLE”列“myCOLUMN”
The actual query is like this, i tried to simplify it a bit in the above example
实际查询是这样的，我试图在上面的例子中简化一下
EXEC('UPDATE myTABLE
SET myCOLUMN = (SELECT Replace('+ @IntegrationGuardian2FullName +','SINGLEQUOTE','''')')
The value of @IntegrationGuardian2FullNameis "OSINGLEQUOTEJOHN DOE". Hope that makes more sense.
的值为@IntegrationGuardian2FullName“OSINGLEQUOTEJOHN DOE”。希望这更有意义。
Can any body help me formatting this query?
任何机构都可以帮我格式化这个查询吗？
回答by OMG Ponies
Use:
用：
EXEC('UPDATE myTABLE
SET myCOLUMN = (SELECT REPLACE(''OSINGLEQUOTEJOHN DOE'',
''SINGLEQUOTE'',
''''''''))')
What you provided needed two single quotes around the string, and what to replace, and additional single quotes because of the single quote escaping necessary for dynamic SQL.
您提供的内容需要字符串周围的两个单引号以及要替换的内容，以及额外的单引号，因为动态 SQL 需要进行单引号转义。
回答by Dylan Beattie
You need to double-escape the single quote inside the quoted string - so use two single quotes to wrap the string, and four (i.e. two escaped quotes) which will be un-escaped into a pair of single quotes when the query is parsed, and then un-escaped againinto the single quote you actually want to insert:
您需要对带引号的字符串内的单引号进行双重转义 - 因此使用两个单引号来包装字符串，以及在解析查询时将未转义为一对单引号的四个（即两个转义引号），然后再次转义到您实际要插入的单引号中：
EXEC('UPDATE myTable SET myColumn = ''John''''O Doe''')
回答by Santa
While OMG Ponies' answer was perfect and helped me as well, something that was not immediately obvious to me is how that worked. Here is how I understood, and may help OP or anyone else still looking, to apply to any situation.
虽然 OMG Ponies 的回答很完美并且对我也有帮助，但对我来说并不是很明显的是它是如何工作的。这是我的理解方式，可以帮助 OP 或其他仍在寻找的人适用于任何情况。
Using a simple example
sp_change_users_login 'update_one', 'LName','Uname'
使用一个简单的例子
sp_change_users_login 'update_one', 'LName','Uname'
Transformed
select ('sp_change_users_login ''update_one '', ''LName '' ,''Uname '' ')
变身
select ('sp_change_users_login ''update_one '', ''LName '' ,''Uname '' ')
All that has been done is, as OMG Ponies said, wrap the entire statement in single quotes('). Then, for every single quote that is already present('), place one more to precede it('). I am not able to bold the single quotes that were added in the second statement, to make it clearly visible.
正如 OMG Ponies 所说，所做的就是将整个语句用单引号 ( ')括起来。然后，对于已经存在的每个单引号 (')，在它前面再放置一个 ( ')。我无法将第二个语句中添加的单引号加粗，以使其清晰可见。
回答by Michael Pakhantsov
UPDATE myTABLE SET myCOLUMN='O''John Doe'
EXEC('UPDATE myTABLE SET myCOLUMN=''O''John Doe''')
