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(dot)Elphick(at)lfix(dot)co(dot)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