Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TTDXi-0007Ur-Sl for pgsql-sql@postgresql.org; Tue, 30 Oct 2012 15:15:51 +0000 Received: from mout.web.de ([212.227.15.4]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TTDXc-0000cv-LO for pgsql-sql@postgresql.org; Tue, 30 Oct 2012 15:15:50 +0000 Received: from [10.0.1.2] ([213.61.228.214]) by smtp.web.de (mrweb103) with ESMTPSA (Nemesis) id 0MhDgb-1Tot9a1jqD-00MphV for ; Tue, 30 Oct 2012 16:15:43 +0100 From: jan zimmek Content-Type: multipart/alternative; boundary="Apple-Mail=_E7A4B5F4-6993-4724-8F6E-0F40ABFF22DC" Subject: Fwd: replace text occurrences loaded from table Date: Tue, 30 Oct 2012 16:15:42 +0100 References: To: "pgsql-sql@postgresql.org" Message-Id: Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\)) X-Mailer: Apple Mail (2.1499) X-Provags-ID: V02:K0:395Yq28fyXLI0N2BEzVxpPVPWnvHUqHvm4sT7jtxbvO mMWNEt0c323+6FNRFQ+DNmztu13UTZPMrEEhQ0MV9+7zhCMoIi LpHXpUMdnRB3KT7lDrjD2F51rPr8D+ZUQndqb8d4OXpKpgxpkP HpJZMT/irNEIOsVLu+MJV8YvpKLw3zqlJCCJ6WZnG2OMBmjF28 nmB3RK1As3HOfmO9zMlYg== X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201210/62 X-Sequence-Number: 36933 --Apple-Mail=_E7A4B5F4-6993-4724-8F6E-0F40ABFF22DC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 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 >=20 > Hello, > you can try somethig like >=20 > UPDATE tmp_messages > SET message =3D regexp_replace( > message, > (SELECT string_agg(var,'|') FROM tmp_vars), > 'XXX', > 'g') >=20 > 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. >=20 > On Tue, Oct 30, 2012 at 1:45 PM, jan zimmek wrote: > hello, >=20 > 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: >=20 > 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); >=20 > select * from tmp_messages; >=20 > my ABC is XYZ -- row 1 > the XYZ is very VAR123 -- row 2 >=20 > now i need to somehow update the rows in tmp_messages, so that after = the update i get the following: >=20 > select * from tmp_messages; >=20 > my XXX is XXX -- row 1 > the XXX is very XXX -- row 2 >=20 > 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 ? >=20 >=20 > best regards > jan >=20 > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >=20 --Apple-Mail=_E7A4B5F4-6993-4724-8F6E-0F40ABFF22DC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1
Von: Igor Romanchenko = <igor.a.romanchenko@gmail.com<= /a>>
An: jan zimmek <jan.zimmek@web.de>

Hello,
you can try somethig = like

UPDATE tmp_messages
SET = message =3D 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


= --Apple-Mail=_E7A4B5F4-6993-4724-8F6E-0F40ABFF22DC--