From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: replace " with nothing
Date: Wed, 11 May 2011 16:22:32 -0500
Message-ID: <20110511212232.GA4796@rice.edu> (raw)
In-Reply-To: <1305147065.1954.5.camel@tony1.localdomain>
References: <1305145780.1954.4.camel@tony1.localdomain>
<1305147065.1954.5.camel@tony1.localdomain>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: replace " with nothing
In-Reply-To: <20110511212232.GA4796@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox