Re: replacing within cells

Mark Nelson wrote:
  >I should have been more clear. I am replacing occurances of text
  >within other text (like paragraphs). So if the cell contains:
  >"Every morning, Mr. Jones smells his tulips" and another cell
  >contains something else Mr. Jones does, and I want to make all
  >instances of Mr. Jones turn to Mr. Johnson, how do I do that? Do
  >I have to extract the whole cell and then search on it and put it
  >back?

If it's a one-off:

$ psql ...
  COPY table TO '/tmp/table.out';
  \q
$ sed -e 's/Mr. Jones/Mr. Johnson/g' </tmp/table.out > /tmp/table.in
$ psql ...
  DELETE FROM table;
  COPY table FROM '/tmp/table.in';

If you will have to do it a lot, you will need to create a function
replace_all() to do it (PL/Perl is probably the language to use because
of Perl's pattern-replacement operators).

Then you could do:

  UPDATE table
    SET paragraph = replace_all(paragraph, 'Mr. Jones', 'Mr. Johnson')
    WHERE paragraph ~ 'Mr. Jones';

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Praying always with all prayer and supplication in the
      Spirit, and watching thereunto with all perseverance
      and supplication for all saints."       Ephesians 6:18