Re: Varchar2, is this strange or not?

Thanks for the answer. But I donot agree with you.

For varchar2 type, oracle uses nonpadded comparison semantics. It means that 'x1' > 'x' and 'x ' > 'x'.

I tried it on Oracle 8.04 Server Version. It works as I expected. I believe it is just a bug in Oracle Lite.

Thanks anyway.

Huiming Li

Sybrand Bakker wrote:
>
> This is not strange. You are implicitly using variable length semantics.
> This means two strings of inequal length are not padded in comparisons. Your
> idea is your where clause is test > 'x '. The varchar data type was meant to
> function as variable length string with fixed length semantics. AFAIK Oracle
> never implemented it.
>
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA
> Huiming Lee <huiming_at_pop21.odn.ne.jp> wrote in message
> news:3860CBA8.A0447D4A_at_pop21.odn.ne.jp...
> > The following is the test I did:
> >
> > create table test (id number(1), text varchar2(10));
> > insert into test values (1, 'x');
> > insert into test values (2, 'x '); --x with a space
> > commit;
> >
> > SQL> select * from test;
> >
> > ID TEXT
> > --------- ----------
> > 1 x
> > 2 x
> >
> > NO problem with this.
> >
> > SQL> select * from test where text = 'x';
> >
> > ID TEXT
> > --------- ----------
> > 1 x
> >
> > SQL> select * from test where text = 'x '; -- x with a space
> >
> > ID TEXT
> > --------- ----------
> > 2 x
> >
> > SO FAR SO GOOD.
> >
> > SQL> select * from test where text > 'x';
> >
> > no rows selected
> >
> > SQL> select * from test where text < 'x';
> >
> > no rows selected
> >
> > SQL>
> >
> > DO HAVE PROBLEM WITH THIS
> >
> > According to my understanding, for varchar2 type, 'x ' > 'x'
> >
> > I am using Oracle Lite.
> >
> > Anybody can give me an explain? Thanks in advance.
> >
> >
> > This is directly from Oracle Document.
> >
> > Nonpadded Comparison Semantics
> >
> > Oracle compares two values character by character up to the first
> > character that differs. The value with the
> > greater character in that position is considered greater. If two values
> > of different length are identical up to the
> > end of the shorter one, the longer value is considered greater. If two
> > values of equal length have no differing
> > characters, then the values are considered equal. Oracle uses nonpadded
> > comparison semantics whenever
> > one or both values in the comparison have the datatype VARCHAR2 or
> > NVARCHAR2.
> >
Received on Thu Dec 23 1999 - 06:47:24 CST