Re: replace " with nothing

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(at)example(dot)com
OtherFirstName | 2 | me2(at)example(dot)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(at)example(dot)com
OtherFirstName | 2 | me2(at)example(dot)com
(2 rows)

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>