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_name by removing name text from it?
如何通过从中删除名称文本来更新last_name?
final out put should be like
最终出局应该是这样的
name | last_name
----------------
AA | aa
BBB | bbbb
.... | .....
.... | .....
3 个解决方案
#1
14
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 beginning of the string it is replacing in, which is the last_name column.
我在这里使用正则表达式。 '^'||名字|| ''建立正则表达式,所以使用'戴维斯麦克戴维斯'的例子,它构建了正则表达式'^ 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/offset method is not idempotent; if you run it again, it will eat more characters off your last name.
您需要在长度上添加两个来创建偏移量,因为substr是从一开始的(+1)并且您想要包含空格(+1)。但是,我更喜欢正则表达式解决方案,即使它可能表现更差,因为我发现它有点自我记录。它具有额外的优势,它是幂等的:如果你在数据库上再次运行它将没有任何效果。 substr / offset方法不是幂等的;如果你再次运行它,它会从你的姓氏中吃掉更多的字符。
#2
5
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
#3
4
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,'')
#1
14
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 beginning of the string it is replacing in, which is the last_name column.
我在这里使用正则表达式。 '^'||名字|| ''建立正则表达式,所以使用'戴维斯麦克戴维斯'的例子,它构建了正则表达式'^ 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/offset method is not idempotent; if you run it again, it will eat more characters off your last name.
您需要在长度上添加两个来创建偏移量,因为substr是从一开始的(+1)并且您想要包含空格(+1)。但是,我更喜欢正则表达式解决方案,即使它可能表现更差,因为我发现它有点自我记录。它具有额外的优势,它是幂等的:如果你在数据库上再次运行它将没有任何效果。 substr / offset方法不是幂等的;如果你再次运行它,它会从你的姓氏中吃掉更多的字符。
#2
5
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
#3
4
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,'')