[SQL] replace " with nothing

2011-05-11 Thread Tony Capobianco 
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-11 Thread Tony Capobianco 
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.
> 
> 



-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-11 Thread Leif Biberg Kristensen 
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 


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

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-11 Thread Ross J. Reedstrom 
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 | [email protected]
 OtherFirstName |2 | [email protected]

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 | [email protected]
 OtherFirstName |2 | [email protected]
(2 rows)

Ross
-- 
Ross Reedstrom, Ph.D. [email protected]
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-11 Thread Ross J. Reedstrom 
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. [email protected]
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-11 Thread Leif Biberg Kristensen 
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:



regards, Leif

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] CROSS JOIN ordering

2011-05-11 Thread Grzegorz Szpetkowski 
I am curious what is proper ordering for cross joining and joining at
all for two tables. I look at the example at
http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html
and there is:

SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-+--+-+---
   1 | a|   1 | xxx
   1 | a|   3 | yyy
   1 | a|   5 | zzz
   2 | b|   1 | xxx
   2 | b|   3 | yyy
   2 | b|   5 | zzz
   3 | c|   1 | xxx
   3 | c|   3 | yyy
   3 | c|   5 | zzz
(9 rows)

I thought that this should be rather:

   1 | a|   1 | xxx
   2 | b|   1 | xxx
   3 | c|   1 | xxx
   1 | a|   3 | yyy
   2 | b|   3 | yyy
   2 | b|   3 | yyy
   1 | a|   5 | zzz
   2 | b|   5 | zzz
   3 | c|   5 | zzz

DROP TABLE t1, t2;
CREATE TABLE t1 (num int, char name);
CREATE TABLE t2 (num int, value varchar(3));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES (1, 'xxx'), (2, 'yyy'), (3, 'zzz');

SELECT * FROM t1 CROSS JOIN t2;
 num | char | num | value
-+--+-+---
   1 | a|   1 | xxx
   2 | b|   1 | xxx
   3 | c|   1 | xxx
   1 | a|   2 | yyy
   2 | b|   2 | yyy
   3 | c|   2 | yyy
   1 | a|   3 | zzz
   2 | b|   3 | zzz
   3 | c|   3 | zzz
(9 rows)

or even (same data as in documentation):

DROP TABLE t1, t2;
CREATE TABLE t1 (num int, char name);
CREATE TABLE t2 (num int, value varchar(3));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES (1, 'xxx'), (3, 'yyy'), (5, 'zzz');

SELECT * FROM t1 CROSS JOIN t2;
 num | char | num | value
-+--+-+---
   1 | a|   1 | xxx
   2 | b|   1 | xxx
   3 | c|   1 | xxx
   1 | a|   3 | yyy
   2 | b|   3 | yyy
   3 | c|   3 | yyy
   1 | a|   5 | zzz
   2 | b|   5 | zzz
   3 | c|   5 | zzz
(9 rows)

Is there any "proper", standard ordering that I can assume for sure ?
Maybe PostgreSQL 8.4/9.0 versions have strict ordering and older
versions are using mixed ordering depends on something I don't know (I
am just guessing).

Thanks in advance.

Regards,
G. Sz.

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql