From the ANSI/ISO SQL Standard:
"3) The comparison of two character strings is determined as follows:
a) Let CS be the collating sequence indicated in Subclause 4.2.3,
''Rules determining collating sequence usage'', based on the declared
types of the two character strings.
b) If the length in characters of X is not equal to the length in
characters of Y, then the shorter string is effectively replaced, for
the purposes of comparison, with a copy of itself that has been extended
to the length of the longer string by concatenation on the right of one
or more pad characters, where the pad character is chosen based on CS.
If CS has the NO PAD characteristic, then the pad character is an
implementation-dependent character different from any character in the
character set of X and Y that collates less than any string under CS.
Otherwise, the pad character is a <space>.
c) The result of the comparison of X and Y is given by the collating
sequence CS."
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Guy Rouillier
> Sent: Wednesday, October 19, 2005 10:56 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
>
> Richard_D_Levine@raytheon.com wrote:
> > Yep. It is not just limited to empty strings; An all blank string,
> > no matter the number of characters, is stored as NULL. And a
>
> I'm no big Oracle fan; I'm trying to convince my company to convert a
> major database to PG. But I can't reproduce what you are saying here.
> What version of Oracle are you using? I just tried this with 9i, and
it
> properly stores the entered number of spaces into the DB. Table t1 is
> defined with a single varchar2(10) column:
>
> INSERT INTO t1 VALUES (' ');
> SELECT LENGTH(f1) FROM t1;
> => 2
>
> > corollary to that idiocy is that a string with two blank characters
> > is not equal to a string with a single blank character in Oracle.
'a
> > ' is not equal to 'a '. 'a ' is not equal to 'a'.
>
> I certainly hope not. If PG is doing that, it's doing the wrong
thing.
> Would you expect 'abc' to be equal to 'a'? Why then would you expect
'a
> ' to be equal to 'a'? A space character is as valid a character as
'b'
> and 'c'. If the user chooses to ignore spaces, he/she can do that
with
> trim functions, but no DBMS should do that blindly.
>
> > Port that to another database. Seen the JOIN syntax? *sigh*
>
> I believe you're referring to the 8i (+) syntax? 9i supports regular
> outer join syntax.
>
> --
> Guy Rouillier
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings