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: 200108151550.f7FFoBma004763@linda.lfix.co.uk (view raw or flat)
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
  >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



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-2014 The PostgreSQL Global Development Group