Обсуждение: replace " with nothing
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.
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. > >
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
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 >
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
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
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
