[SQL] replace text occurrences loaded from table
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] replace text occurrences loaded from table
> -Original Message- > From: [email protected] [mailto:pgsql-sql- > [email protected]] On Behalf Of jan zimmek > Sent: Tuesday, October 30, 2012 7:45 AM > To: [email protected] > Subject: [SQL] replace text occurrences loaded from table > > 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 ? > You may want to consider creating an alternating regular expression and using "regexp_replace(...)" one time per message instead of "replace(...)" three times Not Tested: regexp_replace(message, 'ABC|XYZ|VAR123', 'XXX', 'g') This should at least reduce the amount of overhead checking each expression against each message would incur. If you need even better performance you would need to find some way to "index" the message contents so that for each expression the index can be used to quickly identify the subset of messages that are going to be altered. The full-text-search capabilities of PostgreSQL will probably help here though I am not familiar with them personally. Since you have not shared the true context of your request no alternatives can be suggested. Also, your ability to implement certain algorithms is influenced by the version of PostgreSQL that you are running and which you have also not provided. David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: [SQL] replace text occurrences loaded from table
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
> Betreff: Aw: [SQL] replace text occurrences loaded from table
> Datum: 30. Oktober 2012 14:38:57 MEZ
> An: jan zimmek
>
> 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 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
