Date: Wed, 11 May 2011 16:22:32 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: replace " with nothing
Message-ID: <20110511212232.GA4796@rice.edu>
References: <1305145780.1954.4.camel@tony1.localdomain>
	<1305147065.1954.5.camel@tony1.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1305147065.1954.5.camel@tony1.localdomain>
User-Agent: Mutt/1.5.18 (2008-05-17)

On Wed, May 11, 2011 at 04:51:05PM -0400, Tony Capobianco wrote:
> Ok, I think I found it:
> 
> select translate(firstname,'"','') from members;
> 
> gives me what I want.

Yup, you beat me to the answer. For the archives, if this was a
compatability question (for example, you've got framework code that
atuogenerates things like the above) you can actually create the
function postgresql is looking for:

reedstrm=# select firstname, memberid,emailaddress from members;
   firstname    | memberid |  emailaddress   
----------------+----------+-----------------
 First"Name     |        1 | me@example.com
 OtherFirstName |        2 | me2@example.com

reedstrm=# create function replace (text,text) returns text as $$ select
replace($1,$2,'') $$ language SQL;
CREATE FUNCTION
reedstrm=# select replace(firstname,'"'), memberid,emailaddress from members;    
    replace     | memberid |  emailaddress   
----------------+----------+-----------------
 FirstName      |        1 | me@example.com
 OtherFirstName |        2 | me2@example.com
(2 rows)

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
Connexions                  http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE
> 
> On Wed, 2011-05-11 at 16:29 -0400, Tony Capobianco wrote:
> > We are converting from Oracle to Postgres.  An Oracle script contains
> > this line:
> > 
> >  select replace(firstname,'"'), memberid, emailaddress from members;
> > 
> > in an effort to replace the " with nothing.  How can I achieve the same
> > result with Postgres?
> > 
> > Here's the Postgres error I get:
> > 
> > select replace(firstname,'"'), memberid, emailaddress from members;
> > ERROR:  function replace(character varying, unknown) does not exist
> > LINE 1: select replace(firstname,'"'), memberid, emailaddress from m...
> > 
> > Thanks.
> > 
> > 
> 
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>