Thread: Generating fields in views with search/replace?
Hi, Say I have a table with fields 'template' and 'original_letter', and 'new_selected_letter'. 'template' could be 'abcdefg0abcdefg', original_letter could be 'A' and new_selected_letter could be 'B'. I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg' and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced the '0' with original_letter or new_selected_letter respectively. Sorry for the silly example, but is there a way for me to do this in Postgresql? I'm using Postgresql 9 Thanks. Regards, Asfand Qazi
On Thu, May 5, 2011 at 9:09 AM, Asfand Qazi (Sanger Institute) <aq2.sanger@gmail.com> wrote: > Hi, > > Say I have a table with fields 'template' and 'original_letter', and > 'new_selected_letter'. 'template' could be 'abcdefg0abcdefg', > original_letter could be 'A' and new_selected_letter could be 'B'. > > I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg' > and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced > the '0' with original_letter or new_selected_letter respectively. > > Sorry for the silly example, but is there a way for me to do this in > Postgresql? I'm using Postgresql 9 The mechanics of making a view do something like that is trivial, but your example doesn't make clear why the character '0' is special and is the one translated? merlin
Asfand Qazi (Sanger Institute) wrote: > > Say I have a table with fields 'template' and 'original_letter', and > 'new_selected_letter'. 'template' could be 'abcdefg0abcdefg', > original_letter could be 'A' and new_selected_letter could be 'B'. > > I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg' > and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced > the '0' with original_letter or new_selected_letter respectively. Well, in 8.4.7 you'd use something like: create view xyz as select regexp_replace(template, '0', original_letter) as original, regexp_replace(template, '0', new_selected_letter) as new_selected from template_table; Should be the same in 9.x. (See docs for more info on the regexp_replace() function) HTH. Later, Bosco.
On Thu, May 5, 2011 at 8:37 PM, Bosco Rama <postgres@boscorama.com> wrote: > Asfand Qazi (Sanger Institute) wrote: >> >> Say I have a table with fields 'template' and 'original_letter', and >> 'new_selected_letter'. 'template' could be 'abcdefg0abcdefg', >> original_letter could be 'A' and new_selected_letter could be 'B'. >> >> I want a view where I see 2 fields: 'original' as 'abcdefgAabcdefg' >> and 'new_selected' as 'abcdefgBabcdefg', where the view has replaced >> the '0' with original_letter or new_selected_letter respectively. > > Well, in 8.4.7 you'd use something like: > > create view xyz as > select regexp_replace(template, '0', original_letter) as original, > regexp_replace(template, '0', new_selected_letter) as new_selected > from template_table; > > Should be the same in 9.x. > > (See docs for more info on the regexp_replace() function) > > HTH. > > Later, > Bosco. > Ah.... thanks, that's perfect. I won't be using a '0', I'll be using some non-printable ASCII character in the template, but the concept was the same. Again, many thanks. Regards, Asfand Qazi