pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplace " with nothing
7+ messages / 4 participants
[nested] [flat]
* replace " with nothing
@ 2011-05-11 20:29 Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 3 replies; 7+ messages in thread
From: Tony Capobianco @ 2011-05-11 20:29 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 20:51 Tony Capobianco <tcapobianco@prospectiv.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
2 siblings, 1 reply; 7+ messages in thread
From: Tony Capobianco @ 2011-05-11 20:51 UTC (permalink / raw)
To: pgsql-sql
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.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 20:53 Lew <noone@lewscanon.com>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
2 siblings, 0 replies; 7+ messages in thread
From: Lew @ 2011-05-11 20:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 21:11 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
2 siblings, 1 reply; 7+ messages in thread
From: Leif Biberg Kristensen @ 2011-05-11 21:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 21:22 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Tony Capobianco <tcapobianco@prospectiv.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ross J. Reedstrom @ 2011-05-11 21:22 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 21:25 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Leif Biberg Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 7+ messages in thread
From: Ross J. Reedstrom @ 2011-05-11 21:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replace " with nothing
@ 2011-05-11 21:37 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Leif Biberg Kristensen @ 2011-05-11 21:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-05-11 21:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-11 20:29 replace " with nothing Tony Capobianco <tcapobianco@prospectiv.com>
2011-05-11 20:51 ` Tony Capobianco <tcapobianco@prospectiv.com>
2011-05-11 21:22 ` Ross J. Reedstrom <reedstrm@rice.edu>
2011-05-11 20:53 ` Lew <noone@lewscanon.com>
2011-05-11 21:11 ` Leif Biberg Kristensen <leif@solumslekt.org>
2011-05-11 21:25 ` Ross J. Reedstrom <reedstrm@rice.edu>
2011-05-11 21:37 ` Leif Biberg Kristensen <leif@solumslekt.org>
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