thanks igor, the combination of regexp_replace and string_agg works great for 
me.

the query runs a lot faster than my previous solution and is way easier to 
grasp.

Anfang der weitergeleiteten Nachricht:

> Von: Igor Romanchenko <igor.a.romanche...@gmail.com>
> Betreff: Aw: [SQL] replace text occurrences loaded from table
> Datum: 30. Oktober 2012 14:38:57 MEZ
> An: jan zimmek <jan.zim...@web.de>
> 
> Hello,
> you can try somethig like
> 
> UPDATE tmp_messages
> SET message = regexp_replace(
>    message,
>    (SELECT string_agg(var,'|') FROM tmp_vars),
>    'XXX',
>    'g')
> 
> The idea is to form a single replacement string and to do all the 
> replacements in one go.
> 1 scan to form the replacement string (something like 'ABC|XYZ|VAR123').
> 1 scan and update do all the changes.
> 
> On Tue, Oct 30, 2012 at 1:45 PM, jan zimmek <jan.zim...@web.de> wrote:
> 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
> 
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

Reply via email to