[SQL] replace text occurrences loaded from table

[jan zimmek]

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

Fwd: [SQL] replace text occurrences loaded from table

[jan zimmek]

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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

Re: [SQL] need some magic with generate_series()

[jan zimmek]

hi andreas,

this might give you an idea how to generate series of dates (or other 
datatypes):

select g, (current_date + (g||' month')::interval)::date from 
generate_series(1,12) g;

regards
jan

Am 22.01.2013 um 22:41 schrieb Andreas :

> Hi
> I need a series of month numbers like  201212, 201301 MM to join other 
> sources against it.
> 
> I've got a table that describes projects:
> projects ( id INT, project TEXT, startdate DATE )
> 
> and some others that log events
> events( project_id INT, createdate DATE, ...)
> 
> to show some statistics I have to count events and present it as a view with 
> the project name and the month as MM starting with startdate of the 
> projects.
> 
> My problem is that there probaply arent any events in a month but I still 
> need this line in the output.
> So somehow I need to have a select that generates:
> 
> project 7,201211
> project 7,201212
> project 7,201301
> 
> It'd be utterly cool to get this for every project in the projects table with 
> one select.
> 
> Is there hope?
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql