SQL - remove a substring from column text

Question

I have the following two columns in a Postgres table

name | last_name ---------------- AA | AA aa BBB | BBB bbbb .... | ..... .... | ..... 

How to update last_name by removing name text from it?

final conclusion should look like

 name | last_name ---------------- AA | aa BBB | bbbb .... | ..... .... | ..... 

Answer 1

 UPDATE table SET last_name = regexp_replace(last_name, '^' || name || ' ', ''); 

This removes only one copy from the beginning of the column and correctly deletes the trailing space.

Edit

I use regex here. '^' || name || ' ' '^' || name || ' ' creates a regular expression, so with the example "Davis McDavis", he builds the regular expression '^Davis ' . ^ causes the regular expression to snap to the beginning of the line, so it will match the word "Davis", followed by a space only at the beginning of the line into which it is replaced, in which the last_name column is located.

You can 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 lengths to create the offset, because substr is unidirectional (+1), and you want to include a space (+1). However, I prefer the regex solution, although it's probably worse because I find it somewhat more self-documenting. It has the added benefit of being idempotent: if you run it again in the database, it will have no effect. The substr/offset method is not idempotent; if you run it again, it will contain more characters from your last name.

Answer 2

Not sure about the syntax, but try the following:

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

I suggest checking it first by selecting:

  SELECT REPLACE(last_name,name,'') FROM table 

Answer 3

you need a replacement function, see http://www.postgresql.org/docs/8.1/static/functions-string.html

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