SQL 如何从 postgresql 中的表中删除单引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8587162/
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 do I remove single quotes from a table in postgresql?
提问by seeker
I searched around quite a bit, it would be great if someone could link me to a solution or answer my query. The thing is I have a postgresql table that contains a lot of single quotes and I cant figure out how to get rid of them, because obviously this
我搜索了很多，如果有人可以将我链接到解决方案或回答我的查询，那就太好了。问题是我有一个包含很多单引号的 postgresql 表，我无法弄清楚如何摆脱它们，因为显然这
update tablename set fieldname= NULL where fieldname=' ;
wont work.
不会工作。
回答by Erwin Brandstetter
Better use replace()for this:
最好为此使用replace()：
UPDATE tbl SET col = replace(col, '''', '');
Much faster than regexp_replace()and it replaces "globally" - all occurrences of the search string. The previously accepted answer by @beny23was wrong in this respect. It replaced first occurrences only, would have to be:
比regexp_replace()它替换“全局”快得多- 所有出现的搜索字符串。@beny23先前接受的答案在这方面是错误的。它只替换了第一次出现，必须是：
UPDATE tbl SET col = regexp_replace(col, '''', '', 'g');
Note the additional parameter 'g'for "globally". Read about string functions in the manual.
请注意'g'“全局”的附加参数。阅读手册中的字符串函数。
Aside: the canonical (and SQL standard) way to escape single quotes(') in string literals is to double them (''). Using Posix style escape sequences works, too, of course. Details:
旁白：在字符串文字中转义单引号( ')的规范（和 SQL 标准）方法是将它们加倍 ( '')。当然，使用 Posix 风格的转义序列也有效。细节：
回答by DavidEG
update tablename set fieldname= NULL where fieldname='''' ;
or
或者
update tablename set fieldname= NULL where fieldname=E'\'' ;
