From: | jan zimmek <jan(dot)zimmek(at)web(dot)de> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Fwd: replace text occurrences loaded from table |
Date: | 2012-10-30 15:15:42 |
Message-ID: | ECC7040E-9DA3-4D7B-A829-FC5B0E7CC178@web.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)a(dot)romanchenko(at)gmail(dot)com>
> Betreff: Aw: [SQL] replace text occurrences loaded from table
> Datum: 30. Oktober 2012 14:38:57 MEZ
> An: jan zimmek <jan(dot)zimmek(at)web(dot)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(dot)zimmek(at)web(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Tauno Williams | 2012-11-05 10:54:47 | Re: Date Index |
Previous Message | David Johnston | 2012-10-30 15:08:46 | Re: replace text occurrences loaded from table |