replace text occurrences loaded from table

From: jan zimmek <jan(dot)zimmek(at)web(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: replace text occurrences loaded from table
Date: 2012-10-30 11:45:19
Message-ID: 47098D72-27B8-4634-8010-066B4765FE91@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

hello,

i am actually trying to replace all occurences in a text column with some value, but the occurrences to replace are defined in a table. this is a simplified version of my schema:

create temporary table tmp_vars as select var from (values('ABC'),('XYZ'),('VAR123')) entries (var);
create temporary table tmp_messages as select message from (values('my ABC is XYZ'),('the XYZ is very VAR123')) messages (message);

select * from tmp_messages;

my ABC is XYZ -- row 1
the XYZ is very VAR123 -- row 2

now i need to somehow update the rows in tmp_messages, so that after the update i get the following:

select * from tmp_messages;

my XXX is XXX -- row 1
the XXX is very XXX -- row 2

i have implemented a solution in plpgsql by doing a nested for-loop over tmp_vars and tmp_messages, but i would like to know if there is a more efficient way to solve this problem ?

best regards
jan

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Johnston 2012-10-30 15:08:46 Re: replace text occurrences loaded from table
Previous Message Mark Fenbers 2012-10-29 22:51:16 Re: Fun with Dates