postgresql SQL - 从列的文本中删除子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9477651/
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
SQL - Remove sub string from a column's text
提问by Mithun Sreedharan
I've the following two columns in Postgres table
我在 Postgres 表中有以下两列
name | last_name
----------------
AA | AA aa
BBB | BBB bbbb
.... | .....
.... | .....
How can I update the last_nameby removing nametext from it?
如何last_name通过name从中删除文本来更新？
final out put should be like
最终输出应该是这样的
name | last_name
----------------
AA | aa
BBB | bbbb
.... | .....
.... | .....
回答by Daniel Lyons
UPDATE table SET last_name = regexp_replace(last_name, '^' || name || ' ', '');
This only removes one copy from the beginning of the column and correctly removes the trailing space.
这只会从列的开头删除一个副本并正确删除尾随空格。
Edit
编辑
I'm using a regular expression here. '^' || name || ' 'builds the regular expression, so with the 'Davis McDavis' example, it builds the regular expression '^Davis '. The ^causes the regular expression to be anchored to the beginning of the string, so it's going to match the word 'Davis' followed by a space only at the beginningof the string it is replacing in, which is the last_namecolumn.
我在这里使用正则表达式。'^' || name || ' '构建正则表达式，因此在“Davis McDavis”示例中，它构建了正则表达式'^Davis '。这^会导致正则表达式锚定到字符串的开头，因此它将匹配单词 'Davis' 后跟一个空格，仅在它要替换的字符串的开头，也就是last_name列。
You could achieve the same effect without regular expressions like this:
你可以在没有这样的正则表达式的情况下达到同样的效果：
UPDATE table SET last_name = substr(last_name, length(name) + 2);
You need to add two to the length to create the offset because substr is one-based (+1) and you want to include the space (+1). However, I prefer the regular expression solution even though it probably performs worse because I find it somewhat more self-documenting. It has the additional advantage that it is idempotent: if you run it again on the database it won't have any effect. The substr/offsetmethod is not idempotent; if you run it again, it will eat more characters off your last name.
您需要在长度上添加两个以创建偏移量，因为 substr 从一开始 (+1) 并且您想要包含空格 (+1)。然而，我更喜欢正则表达式解决方案，即使它的性能可能更差，因为我发现它更具有自我记录性。它还有一个额外的优点是它是幂等的：如果你在数据库上再次运行它，它不会有任何影响。该substr/offset方法不是幂等的；如果您再次运行它，它会从您的姓氏中删除更多字符。
回答by lvil
Not sure about syntax, but try this:
不确定语法，但试试这个：
UPDATE table
SET last_name = TRIM(REPLACE(last_name,name,''))
I suggest first to check it by selecting :
我建议首先通过选择来检查它：
SELECT REPLACE(last_name,name,'') FROM table
回答by Vikram
you need the replace function see http://www.postgresql.org/docs/8.1/static/functions-string.html
您需要替换功能，请参阅http://www.postgresql.org/docs/8.1/static/functions-string.html
UPDATE table SET last_name = REPLACE(last_name,name,'')
