Remove sub string from a column's text

Question

I've the following two columns in Postgres table

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

How can I update the last_name by removing name text from it?

final out put should be like

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

Answer 1

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 

Answer 2

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,'')