Fwd: replace text occurrences loaded from table - Mailing list pgsql-sql

From jan zimmek
Subject Fwd: replace text occurrences loaded from table
Date
Msg-id ECC7040E-9DA3-4D7B-A829-FC5B0E7CC178@web.de
Whole thread Raw
List 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.a.romanchenko@gmail.com>
Betreff: Aw: [SQL] replace text occurrences loaded from table
Datum: 30. Oktober 2012 14:38:57 MEZ
An: jan zimmek <jan.zimmek@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.zimmek@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


pgsql-sql by date:

Previous
From: "David Johnston"
Date:
Subject: Re: replace text occurrences loaded from table
Next
From: Adam Tauno Williams
Date:
Subject: Re: Date Index