Remove sub string from a column's text

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.

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.

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 

you need the replace function see http://www.postgresql.org/docs/8.1/static/functions-string.html

UPDATE table SET last_name = REPLACE(last_name,name,'')

• sql
• postgresql