Postgres Pro
Downloads
Home   >   mailing lists

Thread: replace " with nothing

replace " with nothing

From
Tony Capobianco
Date:
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.

Re: replace " with nothing

From
Tony Capobianco
Date:
Ok, I think I found it:

select translate(firstname,'"','') from members;

gives me what I want.

Thanks.

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

Re: replace " with nothing

From
Leif Biberg Kristensen
Date:
On Wednesday 11 May 2011 22:29:40 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...

From the fine documentation
<http://www.postgresql.org/docs/current/static/functions-string.html>

replace(string text, from text, to text)

Example: replace('abcdefabcdef', 'cd', 'XX')

IOW, this function takes three parameters, the first one being the actual text
you want to make a replace on. Yor ecample above shoul probably be written as:

SELECT REPLACE((SELECT firstname FROM members), '%', ''), memberid,
emailaddress FROM members;

although it's a little above me why you would want to select firstname in the
first place when you proceed to replace it with nothing.

regards, Leif

Re: replace " with nothing

From
"Ross J. Reedstrom"
Date:
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
>

Re: replace " with nothing

From
"Ross J. Reedstrom"
Date:
On Wed, May 11, 2011 at 11:11:07PM +0200, Leif Biberg Kristensen wrote:
> 
> although it's a little above me why you would want to select firstname in the 
> first place when you proceed to replace it with nothing.

Nah, he's replacing double-quote-character " with nothing.

An attempt to protect against little Bobby Tables, I assume. (see:
http://xkcd.com/327/ aka SQL injection attacks, but not as fun)

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

Re: replace " with nothing

From
Leif Biberg Kristensen
Date:
On Wednesday 11 May 2011 23:25:34 Ross J. Reedstrom wrote:
> On Wed, May 11, 2011 at 11:11:07PM +0200, Leif Biberg Kristensen wrote:
> > although it's a little above me why you would want to select firstname in
> > the first place when you proceed to replace it with nothing.
> 
> Nah, he's replacing double-quote-character " with nothing.

Yeah, I noticed just a few seconds after I had pressed the "Send" button.
> An attempt to protect against little Bobby Tables, I assume. (see:
> http://xkcd.com/327/ aka SQL injection attacks, but not as fun)

One of my favorite XKCD's. Being a diehard Gentoo user, there's only one that 
beats it:

<http://xkcd.com/456/>

regards, Leif

Re: replace " with nothing

From
Lew
Date:
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...

<http://www.postgresql.org/docs/9.0/interactive/functions-string.html>
<http://www.postgresql.org/docs/9.0/interactive/functions-string.html#FUNCTIONS-STRING-OTHER>

replace(string text, from text, to text)

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg