如何从Postgresql中的表中删除单引号? [英] How do I remove single quotes from a table in postgresql?
问题描述
我搜索了很多东西,如果有人可以将我链接到解决方案或回答我的查询,那将是很棒的。
事情是我有一个包含很多单引号的postgresql表,但我想不出如何去除它们,因为显然这是
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
update tablename set fieldname= NULL where fieldname=' ;
不会工作。
推荐答案
最好使用 replace() > 为此:
Better use replace() for this:
UPDATE tbl SET col = replace(col, '''', '');
比 regexp_replace()
快得多替换全局-搜索字符串的所有匹配项。在这方面,先前接受的@ beny23的答案是错误的。它仅替换了第一次出现的情况,必须是:
Much faster than regexp_replace()
and it replaces "globally" - all occurrences of the search string. The previously accepted answer by @beny23 was wrong in this respect. It replaced first occurrences only, would have to be:
UPDATE tbl SET col = regexp_replace(col, '''', '', 'g');
请注意附加参数'g'
全球。阅读手册中的字符串函数。
Note the additional parameter 'g'
for "globally". Read about string functions in the manual.
此外:在字符串文字中转义单引号('
)的规范(和SQL标准)方法是将它们加倍(''
)。当然,使用Posix样式转义序列也可以。详细信息:
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:
- Insert text with single quotes in PostgreSQL
这篇关于如何从Postgresql中的表中删除单引号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!