Skip site navigation (1) Skip section navigation (2)

Re: replacing within cells

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Mark Nelson <MCN(at)cc(dot)usu(dot)edu>
Cc: PGSQL-NOVICE(at)postgresql(dot)org
Subject: Re: replacing within cells
Date: 2001-08-15 15:50:11
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
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

If it's a one-off:

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

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                    
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

pgsql-novice by date

Next:From: Phillip J. AllenDate: 2001-08-15 15:51:36
Subject: How to Make aggragate Function? Standard Deviation?
Previous:From: Mark NelsonDate: 2001-08-15 14:52:55
Subject: replacing within cells

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group