MySQL 替换字符串中的撇号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16618261/
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
Replacing an apostrophe in a string
提问by JVMX
I have two databases. One has apostrophe in names like O'Bannon and one does not. I need to merge them and find the duplicates. Since it's harder to add the apostrophes I'm tring to remove them instead
我有两个数据库。一种在像 O'Bannon 这样的名字中有撇号，而另一种则没有。我需要合并它们并找到重复项。由于添加撇号更难，我正在尝试将其删除
But this...
但是这个...
UPDATE Client
SET Last_Name = REPLACE(''','')
Clearly won't work. How does one escape the '.
显然行不通。如何摆脱'.
I'm using Xojo (not PHP)
我正在使用 Xojo（不是 PHP）
回答by Erin Schoonover
Like you say, you'll want to escape quote characters.
就像你说的，你会想要转义引号字符。
See this documentation on string literals:
请参阅有关字符串文字的文档：
There are several ways to include quote characters within a string:
A “'” inside a string quoted with “'” may be written as “''”.
A “"” inside a string quoted with “"” may be written as “""”.
Precede the quote character by an escape character (“\”).
A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.
有几种方法可以在字符串中包含引号字符：
用“'”引用的字符串中的“'”可以写成“''”。
用“””引用的字符串中的“””可以写成“””。
在引号字符之前加上转义字符（“\”）。
用“””引用的字符串中的“'”不需要特殊处理，不需要加倍或转义。同样，用“'”引用的字符串中的“””不需要特殊处理。
Depending on how you're dealing with the SQL, though, you may need to do more than that. If the application is escaping the quote character, and passing that to a stored procedure call, you may run into the same issue if you are not using parameter binding with prepared statements. This is due to MySQL removing the escape character upon processing the inputs of the SP. Then the unsantized character makes its way to the query construction and the problem repeats itself if it should be escaped there. In this case, you'll want to switch to parameter binding, so that the escaping and query construction is out of your hands.
但是，根据您处理 SQL 的方式，您可能需要做的不止这些。如果应用程序正在对引号字符进行转义，并将其传递给存储过程调用，并且未将参数绑定与准备好的语句一起使用，则您可能会遇到相同的问题。这是因为 MySQL 在处理 SP 的输入时删除了转义字符。然后未净化的字符进入查询构造，如果应该在那里转义，问题就会重复。在这种情况下，您需要切换到参数绑定，这样转义和查询构造就不受您的控制了。
回答by Ivaylo Strandjev
Simply add an escape character(\) in front of the quote:
只需\在引号前添加一个转义字符 ( )：
SET Last_Name = REPLACE('\'','')
SET Last_Name = REPLACE('\'','')
Still I don't think this is the right way to go as you will lose the information for the original name of the person and so o'reilyand oreilywill seem to be the same surname to you.
不过我不认为这是去为你将失去的人，所以原来的名字的信息以正确的方式o'reily，并oreily会似乎是同姓给你。
回答by Sumoanand
Here we go:
开始了：
UPDATE Client SET Last_Name = REPLACE(Last_Name, '\'', '');
You just need to escape apostrophe will backslash .
你只需要转义撇号就会反斜杠。
回答by Conrad Frix
Table 9.1. Special Character Escape Sequences
表 9.1。特殊字符转义序列
Escape Sequence Character Represented by Sequence
SELECT PhraseId FROM Phrase WHERE Text = REPLACE("don't", "\'", "''")
An ASCII NUL (0x00) character.
\' A single quote (“'”) character.
\" A double quote (“"”) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control+Z). See note following the table.
\ A backslash (“\”) character.
\% A “%” character. See note following the table.
\_ A “_” character. See note following the table.
Of course if ANSI_MODEis not enabled you could use double quotes
当然，如果ANSI_MODE未启用，您可以使用双引号
回答by Thiru
If in case you are just looking to select, i.e., to match a field with data containing apostrophe.
如果您只是想选择，即匹配包含撇号的数据的字段。
##代码##