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